Re: Returning empty on insert

[David Rowley <david.rowley@xxxxxxxxxxxxxxx>]

On Thu, 16 May 2019 at 21:13, Winanjaya Amijoyo
<winanjaya.amijoyo@xxxxxxxxx> 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".

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services