Thread: Returning empty on insert
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
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
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.
———————————————-
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 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
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:
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
Attachment
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:
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: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
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: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
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: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
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