On Thu, 16 May 2019 at 21:13, Winanjaya Amijoyo <winanjaya.amijoyo@gmail.com> wrote: > When record not found then insert and return pid value or if not found then update based on pid and again return its pid.
You could do something like:
WITH s AS ( SELECT pid FROM test WHERE area = 'test5' ), i AS ( INSERT INTO test (area) SELECT 'test5' 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;
but be warned, it's could all fall over as soon as you have concurrent sessions running this at the same time. You could protect against that by doing some advisory locking ( https://www.postgresql.org/docs/11/explicit-locking.html#ADVISORY-LOCKS ), or taking an access exclusive lock on "test".