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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
