Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] Multiply ON CONFLICT ON CONSTRAINT

[GENERAL] Multiply ON CONFLICT ON CONSTRAINT

From
Arnold Somogyi
Date:
Hi,

I wonder if there is a way to add more then one ON CONSTRAINT value.
I have many different unique constraints on my table and I would like to catch and handle two of them with ON CONFLICT ON CONSTRAINT. And the rest errors will be handled by the client application.

INSERT INTO table_1 (id, name, value, user_id, description)
VALUES (1, 'name', 'value', null, null)
ON CONFLICT ON CONSTRAINT table1_pkey, table1_name_key DO UPDATE
SET value = EXCLUDED.value, user_id = EXCLUDED.user_id, description = EXCLUDED.description

Regards,
Arnold

Re: [GENERAL] Multiply ON CONFLICT ON CONSTRAINT

From
Adrian Klaver
Date:
On 02/17/2017 04:53 PM, Arnold Somogyi wrote:
> Hi,
>
> I wonder if there is a way to add more then one ON CONSTRAINT value.
> I have many different unique constraints on my table and I would like to
> catch and handle two of them with ON CONFLICT ON CONSTRAINT. And the
> rest errors will be handled by the client application.
>
> INSERT INTO table_1 (id, name, value, user_id, description)
> VALUES (1, 'name', 'value', null, null)
> ON CONFLICT ON CONSTRAINT *table1_pkey, table1_name_key* DO UPDATE
> SET value = EXCLUDED.value, user_id = EXCLUDED.user_id, description =
> EXCLUDED.description

https://www.postgresql.org/docs/9.6/static/sql-insert.html

"where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate
]
    ON CONSTRAINT constraint_name
"

I read that as only one constraint_name.

The question then becomes whether you want:

table1_pkey, table1_name_key

to OR or AND?

>
> Regards,
> Arnold


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] Multiply ON CONFLICT ON CONSTRAINT

From
Adrian Klaver
Date:
On 02/21/2017 02:49 PM, Arnold Somogyi wrote:

Ccing list.

> I want OR.

I do not think that is possible, then again I have not used this feature
enough to know everything that is possible.

>
>
>
> On Tue, Feb 21, 2017 at 12:33 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/17/2017 04:53 PM, Arnold Somogyi wrote:
>     > Hi,
>     >
>     > I wonder if there is a way to add more then one ON CONSTRAINT value.
>     > I have many different unique constraints on my table and I would like to
>     > catch and handle two of them with ON CONFLICT ON CONSTRAINT. And the
>     > rest errors will be handled by the client application.
>     >
>     > INSERT INTO table_1 (id, name, value, user_id, description)
>     > VALUES (1, 'name', 'value', null, null)
>     > ON CONFLICT ON CONSTRAINT *table1_pkey, table1_name_key* DO UPDATE
>     > SET value = EXCLUDED.value, user_id = EXCLUDED.user_id, description =
>     > EXCLUDED.description
>
>     https://www.postgresql.org/docs/9.6/static/sql-insert.html
>     <https://www.postgresql.org/docs/9.6/static/sql-insert.html>
>
>     "where conflict_target can be one of:
>
>         ( { index_column_name | ( index_expression ) } [ COLLATE
>     collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
>         ON CONSTRAINT constraint_name
>     "
>
>     I read that as only one constraint_name.
>
>     The question then becomes whether you want:
>
>     table1_pkey, table1_name_key
>
>     to OR or AND?
>
>     >
>     > Regards,
>     > Arnold
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com