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
– Frank Heikens, Commented Jun 13, 2023 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
– M-Wajeeh, Commented Jun 13, 2023 at 11:59
This might help you: stackoverflow.com/questions/25307244/… — Huzaifa
– Huzaifa, Commented Jun 13, 2023 at 12:02
It is enough that you make one_row the primary key (keeping the index on true). — klin
– klin, Commented Jun 13, 2023 at 12:21

Frank Heikens · Accepted Answer · 2023-06-13 12:31:14Z

2

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

answered Jun 13, 2023 at 12:31

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

One row table postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related