21

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.

Sky21.86
  • 627
  • 2
  • 9
  • 26
  • See here: http://stackoverflow.com/questions/35888012/use-multiple-conflict-target-in-on-conflict-clause – JF Simon Oct 25 '16 at 16:16
  • 1
    Does this answer your question? [Use multiple conflict\_target in ON CONFLICT clause](https://stackoverflow.com/questions/35888012/use-multiple-conflict-target-in-on-conflict-clause) – Akhilesh Mishra Oct 17 '20 at 12:37

2 Answers2

4

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

jian
  • 4,119
  • 1
  • 17
  • 32
-4

Create a constraint using the above columns like,

CONSTRAINT live_table_ukey UNIQUE(live.table.pkey, live.table.fkey1)

then use this key in on conflict on constraint statement.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
prathyusha bandela
  • 37
  • 4
  • 6
    This may be appropriate for OP (not enough information), but that's not identical to having two unique constraints, one on `pkey` and the other on `fkey1`. – Ivan Vučica Jun 06 '19 at 18:16