column pid is bigserial that I expect to return on both insert and update
I don't want to use ON CONFLICT since it would increasing the sequence although it updating the data
On Thu, May 16, 2019 at 3:26 PM David Rowley <david.rowley@xxxxxxxxxxxxxxx> wrote:
On Thu, 16 May 2019 at 19:56, Winanjaya Amijoyo
<winanjaya.amijoyo@xxxxxxxxx> 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
