1 First Query

Run your first query and check the PostgreSQL server and psql client versions.

The fastest way to confirm your PostgreSQL setup is working is to run a query that needs no tables. PostgreSQL is happy to evaluate expressions on their own — every SELECT does not need a FROM. We will use that property to verify three things in one go: the server is running, your client can reach it, and you have a recent enough version to follow along.

What you’ll learn

How to run a SQL file with psql -f
A minimal SELECT with no table
How to read the PostgreSQL version string
The difference between client and server versions

Example

Example 1.1 First Query

-- Simplest query: no tables, just a literal
SELECT 1;

-- Arithmetic and string expressions work without a FROM
SELECT 2 + 3 AS sum, 'hello, postgres' AS greeting;

-- Check your PostgreSQL server version
SELECT version();

-- A more readable version, when you only need the number
SHOW server_version;

SELECT 1 returns a single row with one column — the integer 1. The expression form (SELECT 2 + 3, 'hello, postgres') shows that any expression can be the body of a SELECT; PostgreSQL evaluates and returns it. version() is a built-in function that returns the full version string (operating system, compiler, etc.), while SHOW server_version returns just the version number. Both report the connected server’s version. psql --version reports the client version, which may differ.

Run the example

To run this file from the repository root:

$ psql -f source/first-query.sql postgres
 ?column?
----------
        1
(1 row)

 sum | greeting
-----+-----------------
   5 | hello, postgres
(1 row)

                                                    version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.x on x86_64-pc-linux-gnu, compiled by gcc ...
(1 row)

 server_version
----------------
 17.x
(1 row)

Your version string will vary. If you see output like above, everything is wired up.

Common pitfalls

“could not connect to server” — the daemon isn’t running. Start it with brew services start postgresql (macOS), sudo systemctl start postgresql (Linux), or whatever your install uses.
“role ‘you’ does not exist” — PostgreSQL doesn’t know about your OS user as a database user. Create one with createuser --superuser $(whoami) from a shell that has access to the cluster.
“database ‘postgres’ does not exist” — some installs (especially homebrew on macOS) don’t create a default postgres database. Run createdb postgres once, or use createdb pbe and pass pbe as the database name to every command.

Tip

If you omit the database name, psql uses your OS username as the default. To use the postgres database explicitly: psql -f source/first-query.sql postgres. To pass a connection string instead, use psql 'postgres://user:pass@host:5432/dbname'.

Try it

Run psql -f source/first-query.sql and confirm the output. Then connect interactively with psql postgres and type SELECT now(); followed by Enter. Exit with \q.

Downloads

SQL file: first-query.sql · All lesson files · Setup guide