Enforce Single Row Table

How to enforce that a table contains at most one row?

Tip: To prevent more than one row from being added to a table, a UNIQUE index over TRUE can be used.

Why would you use this? Good question.
The reason I needed the functionality a few years back was because I was using it as a simple mutex for some concurrent processing. The question has come up before, so there are clearly reasons people are asking how to do it.


CREATE TABLE my_table (
id          INTEGER NOT NULL,
column_a    TEXT NOT NULL
);

-- Show that we can insert a bunch of rows
INSERT INTO my_table VALUES (1,'value 1'), (2, 'value 2'), (3, 'value 3');

SELECT * FROM my_table;
 id | column_a
----+----------
  1 | value 1
  2 | value 2
  3 | value 3
(3 rows)

-- Remove the rows
DELETE FROM ONLY my_table;

-- Add the unique index, to enforce only a single row exists
CREATE UNIQUE INDEX one_row_only_uidx ON my_table (( true ));

-- Attempt to insert more than one row
INSERT INTO my_table VALUES (1,'value 1'), (2, 'value 2'), (3, 'value 3');

-- Fails as expected
ERROR:  duplicate key value violates unique constraint "one_row_only_uidx"
DETAIL:  Key ((true))=(t) already exists.


-- Insert only one row. This will succeed.
INSERT INTO my_table VALUES (99,'value 99');

SELECT * FROM my_table;
 id | column_a
----+----------
 99 | value 99


-- Try to insert another row
INSERT INTO my_table VALUES (33,'value 33');

-- The insert fails as expected.
ERROR:  duplicate key value violates unique constraint "one_row_only_uidx"
DETAIL:  Key ((true))=(t) already exists.

So there you go, a simple trick enforce only one row can exist in a table, without resorting to other options like triggers.