From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Winanjaya Amijoyo <winanjaya(dot)amijoyo(at)gmail(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Returning empty on insert |
Date: | 2019-05-16 14:20:58 |
Message-ID: | 68a33e60-96ef-98ab-49bd-1c5ad9c72ecc@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 5/16/19 7:10 AM, Winanjaya Amijoyo wrote:
> Hi David,
>
> thanks for your advise, as I am new with postgresql..
> I try to use LOCK as below, but it does not returning pid?
> what I missed?
I'm not sure which pid you are referring to, the INSERT or UPDATE or both?
Can you show the output of the query?
>
> BEGIN TRANSACTION;
> LOCK TABLE test IN ACCESS EXCLUSIVE MODE;
> WITH s AS (
> SELECT pid FROM test WHERE area = 'test4'
> ), i AS (
> INSERT INTO test (area)
> SELECT 'test4'
> WHERE NOT EXISTS (SELECT 1 FROM s)
> RETURNING pid
> )
> UPDATE area
> SET last_update = CURRENT_TIMESTAMP
> WHERE pid = (SELECT pid FROM s UNION SELECT pid FROM i)
> RETURNING pid;
> COMMIT TRANSACTION;
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Winanjaya Amijoyo | 2019-05-16 14:25:51 | Re: Returning empty on insert |
Previous Message | Winanjaya Amijoyo | 2019-05-16 14:10:51 | Re: Returning empty on insert |