Re: Returning empty on insert

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(dot)klaver(at)aklaver(dot)com>
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(dot)amijoyo(at)gmail(dot)com <mailto:winanjaya(dot)amijoyo(at)gmail(dot)com>>
> 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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> 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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>