0

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 would like for it to INSERT or UPDATE according to the dm_fkey, which is the foreign key I have set for this table.

This is the UPSERT statment 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
  • Please register/merge your accounts and you'll be able to edit your question and accept an answer. – ypercubeᵀᴹ Jan 9 at 2:16
  • Conflicts happen due to unique or exclusion constraints, not from FOREIGN KEY constraints. Is dg_fkey supposed to be unique? – ypercubeᵀᴹ Jan 9 at 2:18
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

  • 1
    This is NOT because it is "not an index." – ypercubeᵀᴹ Jan 9 at 9:02

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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