Re: UPDATE OR REPLACE?

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