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
suchrequirements, 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
wantis 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
everused. 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