Re: INSERT ... ON CONFLICT DO UPDATE

[Melvin Davidson <melvin6925@xxxxxxxxx>]

Aside from Tom Lane's comments, it seems to me you are reinventing the wheel by generating random values for keys. Why not just use UUID http://www.postgresql.org/docs/9.5/static/datatype-uuid.html

or serial http://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL?

Wouldn't that simplify things by insuring uniqueness?

On Sun, Jul 19, 2015 at 11:12 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 07/19/2015 08:04 AM, Rafal Pietrak wrote:

W dniu 19.07.2015 o 16:33, Adrian Klaver pisze:

On 07/19/2015 06:47 AM, Rafal Pietrak wrote:

Hi,

W dniu 19.07.2015 o 14:10, Geoff Winkless pisze:

On 19 July 2015 at 11:30, Rafal Pietrak <rafal@xxxxxxxxx

[---------------]

Although "a random" can duplicate its previous values, "my random(s)"
(which are created for this application purpose) cannot be duplicated
when it's stored in the database as "live active data". I understand,
that UNIQUE constraint is precisely the RDBMS tool to guarantee that.

 From my perspective the issue is, you are using a 'unique' key generator
that you know is not creating unique keys and then asking the database
to make it right. Sort of like making a square peg fit a round hole by
shaving the corners. It is possible but has sort of a messy feel to it.

Hmmm, yes.

Yet, I don't feel guilty as much, since that is quite similar to a
unique key on database "username", while the "generator" (human mind)
does not guarantee that. The DB just makes sure it does.

I think the argument to be made here is you have no control over what people choose as a username, you do have control over what your key generator outputs.

[--------------]

So an UPSERT is just one feature of ON CONFLICT. The other being DO
NOTHING. Therefore I could see an argument made for adding other ON
CONFLICT clauses. How difficult/plausible that would be is above my
level of expertise.

Mine too. But I'd say that the above wording exactly makes the point I
was trying to make. Thank you.

-R

--
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

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.