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
Best Answer
This because
dg_fkey
is a column referenced in aFOREIGN 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 onON CONFLICT
You probably want something like this,
Or add it the the ddl,
Then your upsert will work.
Also never use double-quotes on identifiers, that's a horrible practice in Pg