14

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
1
  • Conflicts happen due to unique or exclusion constraints, not from FOREIGN KEY constraints. Is dg_fkey supposed to be unique? Jan 9, 2019 at 2:18

2 Answers 2

5

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

2
  • 5
    This is NOT because it is "not an index." Jan 9, 2019 at 9:02
  • Exactly as @ypercubeᵀᴹ sais it has nothing to do with an index. The error is that the ON CONFLICT condition must bu unique, i.e - being a PK or a set of columns that can uniquely identify a row
    – harogaston
    Jan 22, 2021 at 14:48
1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.