Re: Returning empty on insert

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Thu, 16 May 2019 07:47:49 -0700

On 5/16/19 7:26 AM, Winanjaya Amijoyo wrote:
and yes .. I need both inserted pid and updated pid

The INSERT pid is going to be 'swallowed' by the CTE that is why the:

SELECT pid FROM s UNION SELECT pid FROM i

Which also means the UPDATE RETURNING pid will be equal to it.

On Thu, May 16, 2019 at 9:25 PM Winanjaya Amijoyo 
<winanjaya.amijoyo@xxxxxxxxx <mailto:winanjaya.amijoyo@xxxxxxxxx>> wrote:

    see enclosed screenshot..

    I thought, the record still locked that's why it returns empty..

    On Thu, May 16, 2019 at 9:21 PM Adrian Klaver
    <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:

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

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx