Обсуждение: 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?
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
On Thu, Sep 1, 2016 at 12:10 PM, dandl <david@andl.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? Somewhat with a plpgsql function, but with a native UPDATE query, the answer is no. -- Michael
On Thu, Sep 1, 2016 at 12:10 PM, dandl <david@andl.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 invadingmy 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)
> > 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. > 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
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
> > 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. Correct. In practice I don't care whether the action is IGNORE or REPLACE (in Sqlite terms), the outcome is the same. Obviously two different records that share the same primary key is a bad thing and worth an error. Two identical recordsis just boring. Regards David M Bennett FACS Andl - A New Database Language - andl.org
On 09/01/2016 05:08 PM, dandl wrote: >>> 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. > > Correct. In practice I don't care whether the action is IGNORE or REPLACE (in Sqlite terms), the outcome is the same. It is not: https://www.sqlite.org/lang_conflict.html > > Obviously two different records that share the same primary key is a bad thing and worth an error. Two identical recordsis just boring. I do not see how the Sqlite mechanism achieves that. It only looks at UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. It is not looking at the record in its entirety. > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
> >>> 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. > > > > Correct. In practice I don't care whether the action is IGNORE or > REPLACE (in Sqlite terms), the outcome is the same. > > It is not: > https://www.sqlite.org/lang_conflict.html > > > > > Obviously two different records that share the same primary key is a > bad thing and worth an error. Two identical records is just boring. > > I do not see how the Sqlite mechanism achieves that. It only looks at > UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. It is not > looking at the record in its entirety. True: a 'distinct' option is lacking. So for this purpose I use a uniqueness constraint on the whole row. Regards David M Bennett FACS Andl - A New Database Language - andl.org