Postgres by Example: First Query

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

-- 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. The two can disagree if you connect with a newer or older client (psql --version tells you the client's version).

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.

Source: first-query.sql

Next: psql Basics

Home: Postgres by Example