Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification

Question

I currently have a table which looks like this:

CREATE TABLE "PDPC".collection
(
    col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass),
    q1 character varying(10000) COLLATE pg_catalog."default",
    q2 character varying(10000) COLLATE pg_catalog."default",
    q3 character varying(10000) COLLATE pg_catalog."default",
    q4 character varying(10000) COLLATE pg_catalog."default",
    dg_fkey bigint,
    CONSTRAINT collection_pkey PRIMARY KEY (col_no),
    CONSTRAINT collection_dg_fkey_fkey FOREIGN KEY (dg_fkey)
        REFERENCES "PDPC".datagroup (dg_no) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE "PDPC".collection
    OWNER to postgres;

I am trying to execute an UPSERT statement in PHP using postgresql, but i received

Fatal error: Uncaught PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification in C:\Apache24\htdocs\consideration.php:77 Stack trace: #0 C:\Apache24\htdocs\consideration.php(77): PDOStatement->execute() #1 {main} thrown in C:\Apache24\htdocs\consideration.php on line 77

My web page currently has a form that takes in a user input of answers to four questions, and these questions will go into "PDPC".collection table. I want to run INSERT or UPDATE according to the dm_fkey, which is the foreign key I have set for this table.

This is the UPSERT statement that I used.

INSERT INTO "PDPC".collection (q1, q2, q3, q4, dg_fkey)
      VALUES (:q1, :q2, :q3, :q4, :dg_no)
      ON CONFLICT(dg_fkey) DO UPDATE 
      SET q1=:q1, q2=:q2, q3=:q3, q4=:q4

Answer 1

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification in

This because dg_fkey is a column referenced in a FOREIGN KEY CONSTRAINT and not an index. In fact, if you want that to be faster, you may consider additionally adding an index. From the docs on ON CONFLICT

You probably want something like this,

CREATE UNIQUE INDEX asdf ON pdpc.collection(dg_fkey);

Or add it the the ddl,

CREATE TABLE pdpc.collection
(
    dg_fkey bigint UNIQUE

Then your upsert will work.

---

Also never use double-quotes on identifiers, that's a horrible practice in Pg

Answer 2

Had a similar error message. In my case, I thought a table had a UNIQUE constraint but it did not.

I had a query like this:

INSERT ... into <table>
ON CONFLICT ...

Thinking that ON CONFLICT would handle problems when the UNIQUE constraint was violated.

But there was no UNIQUE constraint on the table and therefore the ON CONFLICT made no sense.