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
dg_fkey
supposed to be unique?