Обсуждение: [GENERAL] Missing feature - how to differentiate insert/update in plpgsqlfunction?

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

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

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

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

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

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

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

On Wed, Feb 15, 2017 at 02:58:08PM +0000, Albe Laurenz wrote:
> 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.

Yeah, I'd rather not depend on things like xids for production
(somewhat) code.

Best regards,

depesz

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