Returning empty on insert

column pid is bigserial that I expect to return on both insert and update
I don't want to use ON CONFLICT since it would increasing the sequence
although it updating the data

On Thu, May 16, 2019 at 3:26 PM David Rowley <david.rowley@2ndquadrant.com>
wrote:

On Thu, 16 May 2019 at 19:56, Winanjaya Amijoyo
<winanjaya.amijoyo@gmail.com> wrote:

when running query below, pid returns empty when inserting new record

WITH s AS (
SELECT pid FROM test WHERE area = 'test5'
), i AS (
INSERT INTO test (area)
SELECT 'test5'
WHERE NOT EXISTS (SELECT 1 FROM s)
RETURNING pid
)
UPDATE area
SET last_update = CURRENT_TIMESTAMP
WHERE pid = (SELECT pid FROM s)
RETURNING pid;

Isn't that because you're only inserting a value for the "area"
column. "pid" will end up either NULL or be set to the value of the
column's DEFAULT clause, if it has one.

You might also want to look at INSERT ON CONFLICT DO NOTHING if you
want that INSERT to work in concurrent environments. Docs in
https://www.postgresql.org/docs/current/sql-insert.html

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Thu, 16 May 2019 at 20:28, Winanjaya Amijoyo
<winanjaya.amijoyo@gmail.com> wrote:

column pid is bigserial that I expect to return on both insert and update
I don't want to use ON CONFLICT since it would increasing the sequence although it updating the data

It's not very clear what you're trying to do here.

So, by "pid returns empty", you mean that the UPDATE updates 0 rows,
and you always expect it to update 1 row? The UPDATE is going to
update 0 rows if either "s" is an empty relation, or there's no row in
"area" with the "pid" that's in "s". "s" will be an empty relation if
"test" does not have any row matching WHERE area = 'test5'.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi

What I want to archive here is:

When record not found then insert and return pid value or if not found then
update based on pid and again return its pid.

Please help.

Many thanks in advance.

Regards
Win

On Thu, 16 May 2019 at 15.47 David Rowley <david.rowley@2ndquadrant.com>
wrote:

On Thu, 16 May 2019 at 20:28, Winanjaya Amijoyo
<winanjaya.amijoyo@gmail.com> wrote:

column pid is bigserial that I expect to return on both insert and update
I don't want to use ON CONFLICT since it would increasing the sequence

although it updating the data

It's not very clear what you're trying to do here.

So, by "pid returns empty", you mean that the UPDATE updates 0 rows,
and you always expect it to update 1 row? The UPDATE is going to
update 0 rows if either "s" is an empty relation, or there's no row in
"area" with the "pid" that's in "s". "s" will be an empty relation if
"test" does not have any row matching WHERE area = 'test5'.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Kind regards,
Winanjaya
———————————————-
Sent from my iPhone - Gmail MobIle.
My apologize for the typos & brevity.
———————————————-

On Thu, 16 May 2019 at 21:13, Winanjaya Amijoyo
<winanjaya.amijoyo@gmail.com> wrote:

When record not found then insert and return pid value or if not found then update based on pid and again return its pid.

You could do something like:

WITH s AS (
SELECT pid FROM test WHERE area = 'test5'
), i AS (
INSERT INTO test (area)
SELECT 'test5'
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;

but be warned, it's could all fall over as soon as you have concurrent
sessions running this at the same time. You could protect against
that by doing some advisory locking (
https://www.postgresql.org/docs/11/explicit-locking.html#ADVISORY-LOCKS
), or taking an access exclusive lock on "test".

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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?

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;

On Thu, May 16, 2019 at 4:25 PM David Rowley <david.rowley@2ndquadrant.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

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@aklaver.com>
wrote:

and yes .. I need both inserted pid and updated pid

On Thu, May 16, 2019 at 9:25 PM Winanjaya Amijoyo <
winanjaya.amijoyo@gmail.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.amijoyo@gmail.com <mailto:winanjaya.amijoyo@gmail.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.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

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@aklaver.com>
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@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

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@aklaver.com>
wrote:

On 5/16/19 7:52 AM, Winanjaya Amijoyo wrote:

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

This is good starting point for finding out more:

https://www.postgresql.org/docs/11/queries-with.html#QUERIES-WITH-MODIFYING

--
Adrian Klaver
adrian.klaver@aklaver.com