Re: Returning empty on insert

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(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On Thu, 16 May 2019 at 19:56, Winanjaya Amijoyo
> <winanjaya(dot)amijoyo(at)gmail(dot)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
>