Getting my postgresql db version?

Question

On SQlite I could do a query for PRAGMA user_version; and I could set the version if need be too. Is there anything in postgres that could do the same thing?

I tried select version() but that gets the literal version of postgres, and not a custom set version.

As an update: I researched commenting on databases. Perhaps this could be solution... commenting docs

What I mean is that if I create a fresh Person DB, I can set the user_version=1. Sometime later I create a migration that updates something and then after I set the user_version=2. — Andrew Graham-Yooll
– Andrew Graham-Yooll, Commented May 16, 2017 at 15:47
You can use something like Liquibase to manage your database schema. That will keep track of all changes that you have applied — user330315
– user330315, Commented May 16, 2017 at 16:48

klin · Accepted Answer · 2017-05-16 16:36:37Z

6

You can set a custom configuration parameter. The parameter name must contain a dot, e.g.:

set my.version to 4;
select current_setting('my.version') as version;

 version 
---------
 4
(1 row)

A parameter defined in this way is local to the current session. If you want to define a default value for the parameter for all sessions you can add it to the configuration file postgresql.conf (for all databases in the server). Alternatively, it is possible to set the default value for a database in the command:

set my.version to 4;
alter database my_database set my.version from current;

5 Comments

Andrew Graham-Yooll Over a year ago

And what if I wanted it to persist across sessions?

Andrew Graham-Yooll Over a year ago

Cool! I wont be able to test this out until tomorrow when I can mark it as the answer or not. But conceptually, I don't see why it would not work. Any consideration as to why setting the db version as comment is not a good idea?

klin Over a year ago

Using comments for this purpose is of course possible but clumsy, you have to query a system catalog to retrieve a database comment. Custom parameter is a more natural and easier solution.

Andrew Graham-Yooll Over a year ago

would you happen to know how to remove the custom parameter after it has been set?

klin Over a year ago

I think you cannot remove a local custom parameter (set in a session). For a database - use alter database my_database reset my.version; (the effect will be visible in next sessions, not in the current one)

Collectives™ on Stack Overflow

Getting my postgresql db version?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related