Returning empty on insert

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Returning empty on insert

Winanjaya Amijoyo
Hi all,

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;

what I missed?

please help

many thanks in advance

Regards

Win
Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

David Rowley-3
On Thu, 16 May 2019 at 19:56, Winanjaya Amijoyo
<[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Winanjaya Amijoyo
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 <[hidden email]> wrote:
On Thu, 16 May 2019 at 19:56, Winanjaya Amijoyo
<[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

David Rowley-3
On Thu, 16 May 2019 at 20:28, Winanjaya Amijoyo
<[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Winanjaya Amijoyo
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 <[hidden email]> wrote:
On Thu, 16 May 2019 at 20:28, Winanjaya Amijoyo
<[hidden email]> 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.
———————————————-
Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

David Rowley-3
On Thu, 16 May 2019 at 21:13, Winanjaya Amijoyo
<[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Winanjaya Amijoyo
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 <[hidden email]> wrote:
On Thu, 16 May 2019 at 21:13, Winanjaya Amijoyo
<[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Winanjaya Amijoyo
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 <[hidden email]> 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
[hidden email]

pg1.PNG (53K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

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

On Thu, May 16, 2019 at 9:25 PM Winanjaya Amijoyo <[hidden email]> 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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Adrian Klaver-4
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
> <[hidden email] <mailto:[hidden email]>> 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
>     <[hidden email] <mailto:[hidden email]>> 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
>         [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Winanjaya Amijoyo
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 <[hidden email]> 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
> <[hidden email] <mailto:[hidden email]>> 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
>     <[hidden email] <mailto:[hidden email]>> 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
>         [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Adrian Klaver-4
In reply to this post by Winanjaya Amijoyo
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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Winanjaya Amijoyo
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 <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Returning empty on insert

Adrian Klaver-4
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
[hidden email]