Vryjdfkk

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:Apache24htdocsconsideration.php:77 Stack trace: #0 C:Apache24htdocsconsideration.php(77): PDOStatement->execute() #1 {main} thrown in C:Apache24htdocsconsideration.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

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