Re: Returning empty on insert

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com