yes you are right!.. the insert was swallowed
I tried below:
WITH s AS (
SELECT pid,area FROM test WHERE area = '11'
), i AS (
INSERT INTO test (area)
SELECT '11'
WHERE NOT EXISTS (SELECT 1 FROM s)
RETURNING pid
)
UPDATE test
SET last_update = CURRENT_TIMESTAMP
WHERE pid = (SELECT pid FROM s UNION SELECT pid FROM i) Or pid = (SELECT pid FROM s Where area = '11')
RETURNING pid;
insert does not returning pid
On Thu, May 16, 2019 at 9:51 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 5/16/19 7:25 AM, Winanjaya Amijoyo wrote:
> see enclosed screenshot..
>
> I thought, the record still locked that's why it returns empty..
I'm not sure I believe that screenshot. For one I don't know why it
showing the area and last_update as they are not being returned?
Try the query in psql and see what happens. As an example:
update check_test set fld_1 = '67' where id =1 returning id;
id
----
1
(1 row)
UPDATE 1
>
> 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
