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;
unique on ((1)) is even better :)ReplyDelete
Users can also use the simple functions with security definer. No need for one row table, and have as many columns. Function definition can be changed from SQL script.ReplyDelete
so I think it is even better.
I am using it in our ERP application to maintain the version information.
I'm with Greg on this one.ReplyDelete
I use a column
id integer not null default 1 unique
and then add a check constraint
check (id = 1)
And of course the on delete trigger to prevent the row from being removed.
This in Oracle land... In Postgresql Greg's solution is even nicer.
Ok, I got.ReplyDelete
But... Why just not store a history for the config/version?
The database version is very useful, the config, maybe. But it's a good approach, doesn't it?
Yes, good point. Or even UNIQUE ON ((TRUE)).
Yes, there are other approaches you might take to storing a database revision version. The point of this blog was to illustrate the one-row table, not to grapple with how to manage database revisions.
All these work, but why not just revoke delete on the table?ReplyDelete