Missing feature - how to differentiate insert/update in plpgsql function?
Hi,
I have a function, in PostgreSQL 9.6, which does:
INSERT INTO table () values (...)
ON CONFLICT DO UPDATE ...;
The thing is that the function should return information whether the row
was modified, or created - and currently it seems that this is not
available. Or am I missing something?
Best regards,
depesz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote:
Hi,
I have a function, in PostgreSQL 9.6, which does:INSERT INTO table () values (...)
ON CONFLICT DO UPDATE ...;The thing is that the function should return information whether the row
was modified, or created - and currently it seems that this is not
available. Or am I missing something?
All I can think of is to use:
RETURNING pk
and see if that changed or not.
Best regards,
depesz
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote:
On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote:
Hi,
I have a function, in PostgreSQL 9.6, which does:INSERT INTO table () values (...)
ON CONFLICT DO UPDATE ...;The thing is that the function should return information whether the row
was modified, or created - and currently it seems that this is not
available. Or am I missing something?All I can think of is to use:
RETURNING pk
and see if that changed or not.
Well, this wouldn't work for me as pkey will not change.
For my particular case, I have this table
create table t (
a_from text,
a_to text,
created timestamptz,
updated timestamptz,
primary key (a_from, a_to)
);
where created and updated are set (and kept correct) with triggers. And
in my insert/update, if the row exists, I just set updated to now().
So, for my particular case, I can, and do, compare if created is the
same as updated, and if no - it was update, otherwise - insert.
But it would be really good to get some proper support for
differentiating flow of such queries...
depesz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 02/15/2017 06:27 AM, hubert depesz lubaczewski wrote:
On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote:
On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote:
Hi,
I have a function, in PostgreSQL 9.6, which does:INSERT INTO table () values (...)
ON CONFLICT DO UPDATE ...;The thing is that the function should return information whether the row
was modified, or created - and currently it seems that this is not
available. Or am I missing something?All I can think of is to use:
RETURNING pk
and see if that changed or not.
Well, this wouldn't work for me as pkey will not change.
Alright you lost me. If the pkey does not change then how do you get new
rows(INSERT)?
For my particular case, I have this table
create table t (
a_from text,
a_to text,
created timestamptz,
updated timestamptz,
primary key (a_from, a_to)
);where created and updated are set (and kept correct) with triggers. And
in my insert/update, if the row exists, I just set updated to now().
This I understand, though it does not square with the above.
So, for my particular case, I can, and do, compare if created is the
same as updated, and if no - it was update, otherwise - insert.But it would be really good to get some proper support for
differentiating flow of such queries...depesz
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Feb 15, 2017 at 06:44:09AM -0800, Adrian Klaver wrote:
On 02/15/2017 06:27 AM, hubert depesz lubaczewski wrote:
On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote:
On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote:
Hi,
I have a function, in PostgreSQL 9.6, which does:INSERT INTO table () values (...)
ON CONFLICT DO UPDATE ...;The thing is that the function should return information whether the row
was modified, or created - and currently it seems that this is not
available. Or am I missing something?All I can think of is to use:
RETURNING pk
and see if that changed or not.
Well, this wouldn't work for me as pkey will not change.
Alright you lost me. If the pkey does not change then how do you get new
rows(INSERT)?For my particular case, I have this table
create table t (
a_from text,
a_to text,
created timestamptz,
updated timestamptz,
primary key (a_from, a_to)
);
Well, if I do:
insert into t (a_from, a_to)
and will use some values that do not exist in table, then insert
happens, but not sure what do you mean about "primary key change" in
this case.
On the other hand, if the from/to already exists in the table, then
update happens (on "updated" column) - and then there is definitely no
pkey change.
Best regards,
depesz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
hubert depesz lubaczewski wrote:
I have a function, in PostgreSQL 9.6, which does:
INSERT INTO table () values (...)
ON CONFLICT DO UPDATE ...;The thing is that the function should return information whether the row
was modified, or created - and currently it seems that this is not
available. Or am I missing something?
Maybe the following answer can help:
http://stackoverflow.com/a/39204667/6464308
I don't really know how stable that (undocumented) behaviour will be, though.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Feb 15, 2017 at 06:44:09AM -0800, Adrian Klaver wrote:
Well, this wouldn't work for me as pkey will not change.
Alright you lost me. If the pkey does not change then how do you get new
rows(INSERT)?
I think OP is using natural (rather than surrogate) primary
keys. So, the PK already exists or else is created. But the
(then-returned) _value_ of either is the same.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Feb 15, 2017 at 02:58:08PM +0000, Albe Laurenz wrote:
Maybe the following answer can help:
http://stackoverflow.com/a/39204667/6464308I don't really know how stable that (undocumented) behaviour will be, though.
Yeah, I'd rather not depend on things like xids for production
(somewhat) code.
Best regards,
depesz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 02/15/2017 06:53 AM, hubert depesz lubaczewski wrote:
On Wed, Feb 15, 2017 at 06:44:09AM -0800, Adrian Klaver wrote:
On 02/15/2017 06:27 AM, hubert depesz lubaczewski wrote:
On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote:
On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote:
Hi,
I have a function, in PostgreSQL 9.6, which does:INSERT INTO table () values (...)
ON CONFLICT DO UPDATE ...;The thing is that the function should return information whether the row
was modified, or created - and currently it seems that this is not
available. Or am I missing something?All I can think of is to use:
RETURNING pk
and see if that changed or not.
Well, this wouldn't work for me as pkey will not change.
Alright you lost me. If the pkey does not change then how do you get new
rows(INSERT)?For my particular case, I have this table
create table t (
a_from text,
a_to text,
created timestamptz,
updated timestamptz,
primary key (a_from, a_to)
);Well, if I do:
insert into t (a_from, a_+to)
and will use some values that do not exist in table, then insert
happens, but not sure what do you mean about "primary key change" in
this case.On the other hand, if the from/to already exists in the table, then
update happens (on "updated" column) - and then there is definitely no
pkey change.
Yeah I see(thanks to Karsten also). So:
CREATE TABLE upsert_test (fld_1 varchar,
fld_2 varchar,
PRIMARY KEY (fld_1,-
fld_2));
INSERT INTO upsert_test (fld_1,
fld_2)
VALUES ('test1', 'test3')
ON CONFLICT (fld_1,
fld_2)
DO
UPDATE
SET fld_1 = EXCLUDED.fld_1,
fld_2 = EXCLUDED.fld_2
RETURNING
fld_1,
fld_2;
fld_1 | fld_2
-------+-------
test1 | test3
(1 row)
INSERT 0 1
INSERT INTO upsert_test (fld_1,
fld_2)
VALUES ('test4', 'test5')
ON CONFLICT (fld_1,
fld_2)
DO
UPDATE
SET fld_1 = EXCLUDED.fld_1,
fld_2 = EXCLUDED.fld_2
RETURNING
fld_1,
fld_2;
fld_1 | fld_2
-------+-------
test4 | test5
Can see the differentiation issue now. Can't see a solution right now
other then the one you already have, a marker field that you can use to
determine INSERT/UPDATE.
Best regards,
depesz
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general