I am trying to create a table which can only hold one row. Following table works fine and you can't insert more than one row in this table. But when I try to update that row using ON CONFLICT
, it fails.
CREATE TABLE IF NOT EXISTS one_row_table
(
one_row INT NOT NULL DEFAULT 1,
id varchar(255) NOT NULL
);
CREATE UNIQUE INDEX one_row_only_uidx_one_row_table ON one_row_table (( true ));
insert into one_row_table (id) values ('2');
insert into one_row_table (id) values ('3') ON CONFLICT (one_row) DO UPDATE SET id = EXCLUDED.id;
On last insert I get ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
. Can someone please explain what am I doing wrong here and how can I fix it? I searched and also looked at the documentation here https://www.postgresql.org/docs/current/sql-insert.html but I still don't understand the issue.
This one works, with a generated column that cannot be set by the user:
CREATE TABLE IF NOT EXISTS one_row_table (
one_row BOOL GENERATED ALWAYS AS ( TRUE ) STORED
, ID VARCHAR ( 255 ) NOT NULL
);
CREATE UNIQUE INDEX one_row_only_uidx_one_row_table ON one_row_table ( ( one_row ) );
INSERT INTO one_row_table ( ID ) VALUES ( '2' )
ON conflict ( one_row ) DO
UPDATE
SET ID = EXCLUDED.ID
RETURNING *;
INSERT INTO one_row_table ( ID ) VALUES ( '3' )
ON conflict ( one_row ) DO
UPDATE
SET ID = EXCLUDED.ID RETURNING *;
-- fails because of the manual setting for "one_row":
INSERT INTO one_row_table ( one_row, ID ) VALUES (false, '1' )
ON conflict ( one_row ) DO
UPDATE
SET ID = EXCLUDED.ID
RETURNING *;