On 5/16/19 7:26 AM, Winanjaya Amijoyo wrote:
and yes .. I need both inserted pid and updated pid
The INSERT pid is going to be 'swallowed' by the CTE that is why the: SELECT pid FROM s UNION SELECT pid FROM i Which also means the UPDATE RETURNING pid will be equal to it.
On Thu, May 16, 2019 at 9:25 PM Winanjaya Amijoyo <[email protected] <mailto:[email protected]>> wrote:see enclosed screenshot.. I thought, the record still locked that's why it returns empty.. On Thu, May 16, 2019 at 9:21 PM Adrian Klaver <[email protected] <mailto:[email protected]>> wrote: On 5/16/19 7:10 AM, Winanjaya Amijoyo wrote: > Hi David, > > thanks for your advise, as I am new with postgresql.. > I try to use LOCK as below, but it does not returning pid? > what I missed? I'm not sure which pid you are referring to, the INSERT or UPDATE or both? Can you show the output of the query? > > BEGIN TRANSACTION; > LOCK TABLE test IN ACCESS EXCLUSIVE MODE; > WITH s AS ( > SELECT pid FROM test WHERE area = 'test4' > ), i AS ( > INSERT INTO test (area) > SELECT 'test4' > WHERE NOT EXISTS (SELECT 1 FROM s) > RETURNING pid > ) > UPDATE area > SET last_update = CURRENT_TIMESTAMP > WHERE pid = (SELECT pid FROM s UNION SELECT pid FROM i) > RETURNING pid; > COMMIT TRANSACTION; > >-- Adrian Klaver[email protected] <mailto:[email protected]>
-- Adrian Klaver [email protected]
