Menu
In am currently working with PostgreSQL 9.5 and was wondering if the is a possibility to include names of 2 constraints in the ON CONFLICT ON CONSTRAINT statement. My sql is below
INSERT INTO LIVE.TABLE (column1, column2, column3)
SELECT DISTINCT ON (cloumn1) column1, column2, column3
FROM STAGE.TABLE
ON CONFLICT ON CONSTRAINT live.table.pkey DO NOTHING
This works fine however what i am trying to do is to include second constraint in ON CONFLICT ON CONSTRAINT statement. I have tried below option but it does not seem to work for me.
INSERT INTO LIVE.TABLE (column1, column2, column3)
SELECT DISTINCT ON (cloumn1) column1, column2, column3
FROM STAGE.TABLE
ON CONFLICT ON CONSTRAINT live.table.pkey, live.table.fkey1 DO NOTHING
Any suggestion will be highly appreciated.
521
Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. The syntax is: CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) ); See also CREATE TABLE ...
ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action. conflict_target can perform unique index inference.
To find the name of a constraint in PostgreSQL, use the view pg_constraint in the pg_catalog schema. Join the view pg_catalog. pg_constraint with the view pg_class ( JOIN pg_class t ON t. oid = c.
PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.
Seems you cannot attach two constraint name like
ON CONFLICT ON CONSTRAINT live.table.pkey, live.table.fkey1 DO NOTHING
But you can
ON CONFLICT ( col1, col2 ) DO NOTHING.or
ON CONFLICT DO NOTHING.Specifies which conflicts ON CONFLICT takes the alternative action on by choosing arbiter indexes. Either performs unique index inference, or names a constraint explicitly. For ON CONFLICT DO NOTHING, it is optional to specify a conflict_target; when omitted, conflicts with all usable constraints (and unique indexes) are handled. For ON CONFLICT DO UPDATE, a conflict_target must be provided.
https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
