UPDATE OR REPLACE?

On Thu, Sep 1, 2016 at 12:10 PM, dandl <david@andl.org> wrote:

Sqlite has options to handle an update that causes a duplicate key. Is there
anything similar in Postgres?
This is not an UPSERT. The scenario is an UPDATE that changes some key field
so that there is now a duplicate key. In Sqlite this handled as:
UPDATE OR IGNORE table SET <etc>
UPDATE OR REPLACE table SET <etc>

And so on

See https://www.sqlite.org/lang_update.html.

Can Postgres do this?

Somewhat with a plpgsql function, but with a native UPDATE query, the
answer is no.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Sep 1, 2016 at 12:10 PM, dandl <david@andl.org> wrote:

Sqlite has options to handle an update that causes a duplicate key. Is
there anything similar in Postgres?
This is not an UPSERT. The scenario is an UPDATE that changes some key
field so that there is now a duplicate key. In Sqlite this handled as:
UPDATE OR IGNORE table SET <etc>
UPDATE OR REPLACE table SET <etc>

And so on

See https://www.sqlite.org/lang_update.html.

Can Postgres do this?

I would propose that this effectively violates referential integrity and shouldn't be a valid design pattern.

In my mind primary keys are supposed to be static, stable, non-volatile...aka predictable. It feels like an alien invading my schema, to contemplate such an activity. I hope PG never supports that.

Postgres allows developers incredible freedom to do really crazy things. That doesn't mean that they should.

Mike Sofen (USA)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sqlite has options to handle an update that causes a duplicate key.

Is

there anything similar in Postgres?
This is not an UPSERT. The scenario is an UPDATE that changes some

key

field so that there is now a duplicate key. In Sqlite this handled

as:

UPDATE OR IGNORE table SET <etc>
UPDATE OR REPLACE table SET <etc>

And so on

See https://www.sqlite.org/lang_update.html.

Can Postgres do this?

I would propose that this effectively violates referential integrity
and shouldn't be a valid design pattern.

In my mind primary keys are supposed to be static, stable, non-
volatile...aka predictable. It feels like an alien invading my
schema, to contemplate such an activity. I hope PG never supports
that.

It's an interesting proposition, but not one I fear will find universal support. The relational model itself has no such requirements, and there are perfectly valid tables that have no primary key, but use a constraint to forbid duplicates. A link table implementing an N:M relationship is one such.

In my particular situation the case I care about is when the result of an UPDATE is two identical rows. All I really want is a DISTINCT option.

Postgres allows developers incredible freedom to do really crazy
things. That doesn't mean that they should.

To the best of my knowledge and belief that statement could be made about every serious programming language I've ever used. Why should Postgres SQL be any different?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 09/01/2016 07:37 AM, dandl wrote:

Sqlite has options to handle an update that causes a duplicate key.

Is

there anything similar in Postgres?
This is not an UPSERT. The scenario is an UPDATE that changes some

key

field so that there is now a duplicate key. In Sqlite this handled

as:

UPDATE OR IGNORE table SET <etc>
UPDATE OR REPLACE table SET <etc>

And so on

See https://www.sqlite.org/lang_update.html.

Can Postgres do this?

I would propose that this effectively violates referential integrity
and shouldn't be a valid design pattern.

In my mind primary keys are supposed to be static, stable, non-
volatile...aka predictable. It feels like an alien invading my
schema, to contemplate such an activity. I hope PG never supports
that.

It's an interesting proposition, but not one I fear will find universal support. The relational model itself has no such requirements, and there are perfectly valid tables that have no primary key, but use a constraint to forbid duplicates. A link table implementing an N:M relationship is one such.

In my particular situation the case I care about is when the result of an UPDATE is two identical rows. All I really want is a DISTINCT option.

Assuming I am following correctly what you want is that the result of an
UPDATE not be two identical rows.

Postgres allows developers incredible freedom to do really crazy
things. That doesn't mean that they should.

To the best of my knowledge and belief that statement could be made about every serious programming language I've ever used. Why should Postgres SQL be any different?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

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

In my particular situation the case I care about is when the result

of an UPDATE is two identical rows. All I really want is a DISTINCT
option.

Assuming I am following correctly what you want is that the result of
an UPDATE not be two identical rows.

Correct. In practice I don't care whether the action is IGNORE or REPLACE (in Sqlite terms), the outcome is the same.

Obviously two different records that share the same primary key is a bad thing and worth an error. Two identical records is just boring.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 09/01/2016 05:08 PM, dandl wrote:

In my particular situation the case I care about is when the result

of an UPDATE is two identical rows. All I really want is a DISTINCT
option.

Assuming I am following correctly what you want is that the result of
an UPDATE not be two identical rows.

Correct. In practice I don't care whether the action is IGNORE or REPLACE (in Sqlite terms), the outcome is the same.

It is not:
https://www.sqlite.org/lang_conflict.html

Obviously two different records that share the same primary key is a bad thing and worth an error. Two identical records is just boring.

I do not see how the Sqlite mechanism achieves that. It only looks at
UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. It is not looking
at the record in its entirety.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

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

In my particular situation the case I care about is when the

result

of an UPDATE is two identical rows. All I really want is a DISTINCT
option.

Assuming I am following correctly what you want is that the result

of

an UPDATE not be two identical rows.

Correct. In practice I don't care whether the action is IGNORE or

REPLACE (in Sqlite terms), the outcome is the same.

It is not:
https://www.sqlite.org/lang_conflict.html

Obviously two different records that share the same primary key is a

bad thing and worth an error. Two identical records is just boring.

I do not see how the Sqlite mechanism achieves that. It only looks at
UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. It is not
looking at the record in its entirety.

True: a 'distinct' option is lacking. So for this purpose I use a uniqueness constraint on the whole row.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general