On Thu, 16 May 2019 at 19:56, Winanjaya Amijoyo
<winanjaya.amijoyo@gmail.com> wrote:
> when running query below, pid returns empty when inserting new record
>
> 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)
> RETURNING pid;
Isn't that because you're only inserting a value for the "area"
column. "pid" will end up either NULL or be set to the value of the
column's DEFAULT clause, if it has one.
You might also want to look at INSERT ON CONFLICT DO NOTHING if you
want that INSERT to work in concurrent environments. Docs in
https://www.postgresql.org/docs/current/sql-insert.html
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services