How can I confirm a database is Postgres & what version it is using SQL?

Question

I'm building an installer for an application. The user gets to select a datasource they have configured and nominate what type of database it is. I want to confirm that the database type is indeed Postgres, and if possible, what version of Postgres they are running by sending a SQL statement to the datasource.

Stop duplicating questions! <stackoverflow.com/questions/101184/…> — gizmo
– gizmo, Commented Sep 19, 2008 at 11:31
They're not duplicate questions. My research to date suggests you need quite different approaches to this problem. — modius
– modius, Commented Sep 19, 2008 at 11:34

Matthias Kestenholz · Accepted Answer · 2008-09-19 11:33:30Z

4

Try this:

mk=# SELECT version();
                                            version                                            
-----------------------------------------------------------------------------------------------
 PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)
(1 row)

The command works too in MySQL:

mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.0.32-Debian_7etch1~bpo.1-log | 
+--------------------------------+
1 row in set (0.01 sec)

There is no version command in sqlite as far as I can see.

answered Sep 19, 2008 at 11:33

Matthias Kestenholz

3,3681 gold badge23 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Milen A. Radev · Accepted Answer · 2008-09-19 11:45:24Z

4

SHOW server_version;

(for completeness)

answered Sep 19, 2008 at 11:45

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Comments

Galwegian · Accepted Answer · 2008-09-19 11:32:28Z

2

SELECT version();

answered Sep 19, 2008 at 11:32

Galwegian

42.4k16 gold badges115 silver badges158 bronze badges

Comments

Neall · Accepted Answer · 2008-09-19 11:33:41Z

2

PostgreSQL has a version() function you can call.

SELECT version();

It will return something like this:

                                            version
-----------------------------------------------------------------------------------------------
PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)

answered Sep 19, 2008 at 11:33

Neall

27.3k5 gold badges51 silver badges48 bronze badges

Comments

Vinko Vrsalovic · Accepted Answer · 2008-09-19 11:34:10Z

2

This is DB dependent, and in case this function exists in another dbms, this says PostgreSQL in the output

select version()

answered Sep 19, 2008 at 11:34

Vinko Vrsalovic

342k55 gold badges341 silver badges374 bronze badges

Comments

Learning · Accepted Answer · 2008-09-19 11:50:38Z

0

Interesting ... version() is a function! I wonder why? Version is not going to change or return different values under different inputs/circumstances.

Curious because I remember from old days that in Sybase it used to be a global variable and version could be found out by doing "select @@version"

answered Sep 19, 2008 at 11:50

Learning

8,2133 gold badges39 silver badges47 bronze badges

Collectives™ on Stack Overflow

How can I confirm a database is Postgres & what version it is using SQL?

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related