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.row...@2ndquadrant.com> wrote: > On Thu, 16 May 2019 at 19:56, Winanjaya Amijoyo > <winanjaya.amij...@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 >
