Re: UPDATE OR REPLACE?

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(dot)klaver(at)aklaver(dot)com