and yes .. I need both inserted pid and updated pid On Thu, May 16, 2019 at 9:25 PM Winanjaya Amijoyo < 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> > 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 >> >
