Re: Returning empty on insert

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Winanjaya Amijoyo <winanjaya(dot)amijoyo(at)gmail(dot)com>
Cc: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Returning empty on insert
Date: 2019-05-16 15:03:21
Message-ID: 5522a720-7822-9e7f-d7ce-69ba7f3d9669@aklaver.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 5/16/19 7:52 AM, Winanjaya Amijoyo wrote:
> yes you are right!.. the insert was swallowed
>
> I tried below:
>
> WITH s AS (
>    SELECT pid,area FROM test WHERE area = '11'
> ), i AS (
>    INSERT INTO test (area)
>    SELECT '11'
>    WHERE NOT EXISTS (SELECT 1 FROM s)
>    RETURNING pid
> )
> UPDATE test
> SET last_update = CURRENT_TIMESTAMP
> WHERE pid =  (SELECT pid FROM s UNION SELECT pid FROM i) Or pid =
> (SELECT pid FROM s Where area = '11')
> RETURNING pid;
>
> insert does not returning pid
>

This is good starting point for finding out more:

https://www.postgresql.org/docs/11/queries-with.html#QUERIES-WITH-MODIFYING

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Daulat Ram 2019-05-16 15:31:00 bigint out of range
Previous Message Winanjaya Amijoyo 2019-05-16 14:52:58 Re: Returning empty on insert