Skip to main content
0 reactions

How to Check Your PostgreSQL Version

Featured card titled Check Your PostgreSQL Version, with a terminal window listing psql --version, its answer psql (PostgreSQL) 19.0, SHOW server_version; and its answer 19.0.
Check the psql and server versions of PostgreSQL.

Guides and extensions often work only with certain PostgreSQL versions, so it’s worth knowing which one you have. There are two versions to check: psql, the command-line client on your computer, and the PostgreSQL server you connect to. One command shows each.

Every command and output on this page is real, from PostgreSQL 19 on macOS.

Syntax

Syntax (Terminal)Bash
psql --version
Syntax (psql)SQL
SHOW server_version;
SELECT version();
  • psql --version: prints the version of psql, the client. You run it in Terminal, and it doesn’t connect to anything.
  • SHOW server_version;: run inside psql. SHOW reads one of the server’s settings, and server_version holds the version of the server you’re connected to.
  • SELECT version();: also run inside psql. version() is a built-in function that returns the full version text, with the platform and compiler.

These come from the PostgreSQL documentation: psql (opens in a new tab), SHOW (opens in a new tab) and System Information Functions (opens in a new tab).

Step 1: Check the psql Version

Open Terminal and run:

TerminalBash
psql --version
OutputPlain text
psql (PostgreSQL) 19.0

That’s the version of psql itself. If Terminal says zsh: command not found: psql, PostgreSQL’s bin folder isn’t on your PATH yet: How to Add a Folder to PATH on macOS (zsh) shows how to add it.

Step 2: Check the PostgreSQL Server Version

Connect to your server with psql, as Part 2 of the PostgreSQL Tutorial shows, then run this at the postgres=# prompt:

postgres=#SQL
SHOW server_version;
OutputPlain text
 server_version
----------------
 19.0
(1 row)

The server runs PostgreSQL 19.0. Here it matches psql, because both came with the same install.

Step 3: See the Full Version Details

postgres=#SQL
SELECT version();
OutputPlain text
                                                      version
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 19.0 on aarch64-apple-darwin24.6.0, compiled by Apple clang version 17.0.0 (clang-1700.0.13.5), 64-bit
(1 row)

The text adds the platform the server was built for, aarch64-apple-darwin (a Mac with Apple silicon), and the compiler that built it.

Common Mistakes

MistakeWhat happensDo this instead
Leaving out the parentheses: SELECT version;ERROR: column "version" does not existWrite SELECT version();. The parentheses tell PostgreSQL to call the function.
Asking for a setting called version: SHOW version;ERROR: unrecognized configuration parameter "version"The setting is server_version: run SHOW server_version;.

FAQ

Can psql and the server have different versions?

Yes. They’re separate programs, and psql can connect to a server on another computer. psql --version shows only psql’s version, and SHOW server_version; shows the server’s.

How do I get the version as a number?

Ask for server_version_num. Scripts use it because it’s easy to compare:

postgres=#SQL
SHOW server_version_num;
OutputPlain text
 server_version_num
--------------------
 190000
(1 row)

190000 means 19.0.

Can I check the server version without connecting?

Yes. postgres is the server program, and on a Mac it’s in the same folder as psql:

TerminalBash
postgres --version
OutputPlain text
postgres (PostgreSQL) 19.0

What do the numbers in 19.0 mean?

19 is the major version, released once a year with new features, and 0 is the minor release, which brings only fixes. PostgreSQL Versioning Policy Explained covers the details.

Conclusion

Run psql --version in Terminal to see psql’s version, and SHOW server_version; in psql to see the server’s. SELECT version(); adds the platform and compiler when you need them.

Additional Resources

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

0 comments