databasepostgresqlunique-constraint

One row table postgres


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.


Solution

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