Re: UPDATE OR REPLACE?

On Thu, Sep 1, 2016 at 12:10 PM, dandl <david(at)andl(dot)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)