I changed the query to: LOCK TABLE test IN EXCLUSIVE MODE; WITH s AS ( SELECT pid,area FROM test WHERE area = 'test123' ), i AS ( INSERT INTO test (area) SELECT ' test123 ' 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) Or pid = (SELECT pid FROM s Where area = ' test123') RETURNING pid;
now it returns for both insert and update .. but if I use BEGIN WORK; and COMMIT WORK; before and end of it .. it returns empty On Thu, May 16, 2019 at 9:47 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote: > 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 > > <winanjaya.amij...@gmail.com <mailto:winanjaya.amij...@gmail.com>> > 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 > > <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> > 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 > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
