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 ()
LANGUAGE plpgsql AS $f$
RAISE EXCEPTION 'You may not delete the DB version!';
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();
postgres=# delete from db_version;
ERROR: You may not delete the DB version!
STATEMENT: delete from db_version;