Creating a Table With Exactly One Row

Here's a situation which comes up a lot in database design: the one-row table.  That is, a table designed to hold one and only one row, which contains some settings or metadata about the database or application.  For example, imagine that we want to have a one-row table which holds the current database revision version and the date it was last upgraded:

CREATE TABLE db_version (

    version NUMERIC NOT NULL,

    upgraded_on TIMESTAMPTZ NOT NULL

);

Since the database is only one specific version at any time, it would cause problems for the upgrade scripts if there were more than one row in this table.  Which row should they believe?  So you want to ensure that there's never more than one row.

You could, of course, do this using a trigger.  However, a trigger is complex, and a BEFORE INSERT trigger needs to either count the rows, or be installed after a sample row is added.  Either is more work than it needs to be.  Try this instead:

CREATE UNIQUE INDEX db_version_one_row

ON db_version((version IS NOT NULL));

This works because the version column is already declared NOT NULL.  Since you've created a unique index on the NOT NULL expression, you can never insert a second row because it would violate the uniqueness condition.

Unfortunately, there's no clever trick to preventing that one row from being deleted; that requires a BEFORE DELETE trigger:

CREATE FUNCTION db_version_no_delete ()

RETURNS trigger

LANGUAGE plpgsql AS $f$

BEGIN

   RAISE EXCEPTION 'You may not delete the DB version!';

END; $f$;

CREATE TRIGGER db_version_no_delete

BEFORE DELETE ON db_version

FOR EACH ROW EXECUTE PROCEDURE db_version_no_delete();

Now this all works as expected; you can insert the first row to start the table, and only update it after that:

postgres=# INSERT INTO db_version VALUES ( '1.0',now());
INSERT 0 1

postgres=# INSERT INTO db_version VALUES ( '1.1',now());
ERROR:  duplicate key value violates unique constraint "db_version_one_row"
DETAIL:  Key ((version IS NOT NULL))=(t) already exists.

postgres=# UPDATE db_version SET version = '1.1', upgraded_on = now();
UPDATE 1

postgres=# delete from db_version;
ERROR:  You may not delete the DB version!
STATEMENT:  delete from db_version;