Re: Returning empty on insert

[Adrian Klaver <adrian.klaver@xxxxxxxxxxx>]

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.klaver@xxxxxxxxxxx