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