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.

The unique constraint should be on the column one_row, not the fixed value "true" — Frank Heikens, Jun 13 '23 at 11:57
I see but then it won't restrict the number of rows to one. I guess I can make `one_row` to be primary key and then never provide this value from the code. This way it will always be 1 and single row will be enforced. — M-Wajeeh, Jun 13 '23 at 11:59
This might help you: https://stackoverflow.com/questions/25307244/how-to-allow-only-one-row-for-a-table — Huzaifa, Jun 13 '23 at 12:02
It is enough that you make `one_row` the primary key (keeping the index on `true`). — klin, Jun 13 '23 at 12:21

score 2 · Accepted Answer · answered Jun 13 '23 at 12:31

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

One row table postgres

1 Answers1