One row table postgres

Question

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.

Answer 1

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 *;