Search Postgresql Archives

Re: Returning empty on insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I changed the query to:

LOCK TABLE test IN EXCLUSIVE MODE;
WITH s AS (
   SELECT pid,area FROM test WHERE area = 'test123'
), i AS (
   INSERT INTO test (area)
   SELECT ' test123 '
   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) Or pid = (SELECT pid FROM s Where area = ' test123')
RETURNING pid;

now it returns for both insert and update ..
but if I use BEGIN WORK; and COMMIT WORK; before and end of it .. it returns empty


On Thu, May 16, 2019 at 9:47 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux