The psql Terminal

Objectives

By the end of this lesson, you should be able to:

• Connect to PostgreSQL using psql
• Use a handful of essential psql meta-commands to look around
• Run a plain SQL statement at the psql prompt

💡 Why this matters: psql is PostgreSQL’s official command-line client, fast, scriptable, and available anywhere you can open a terminal. Plenty of real database work, including everything demonstrated in this course, happens through it.

⚠️ A note on verification: the SQL commands and results shown throughout this course were run against a real, live PostgreSQL 18 engine, the same engine psql connects to. psql’s own interactive behavior described here is standard, documented PostgreSQL behavior.

Connecting with psql

From a terminal, connect with:

psql -U postgres

-U postgres connects as the postgres superuser account (created automatically during installation). You may be prompted for the password you set during installation. A successful connection drops you into a prompt like this:

postgres=#

That # means you’re connected as a superuser and ready to type SQL, or psql’s own special commands.

psql Meta-Commands

psql has its own commands, separate from SQL, for looking around a database. They all start with a backslash, and don’t end with a semicolon:

\l              list all databases
\c dbname       connect to a different database
\dt             list all tables in the current database
\d tablename    describe one table's columns and types
\q              quit psql
\?              show help for all psql commands

For example, \dt lists every table in whatever database you’re currently connected to, and \d tablename shows exactly what columns a specific table has, along with their types and constraints, this is one of the most common things you’ll do while working with a database, check what a table actually looks like before writing a query against it.

Running SQL at the Prompt

Plain SQL statements work directly at the same prompt, and, unlike meta-commands, do end with a semicolon:

SELECT version();

                                                version
---------------------------------------------------------------------------------------------
 PostgreSQL 18.3 on x86_64-pc-linux-gnu, compiled by gcc, 64-bit

Everything from Module 2 onward in this course is exactly this: SQL statements, typed at a prompt like this one (or into pgAdmin’s Query Tool from the last lesson), ending in a semicolon.

Try It

1. Connect using psql -U postgres and confirm you reach the postgres=# prompt.
2. Run \l and see what databases already exist (a fresh install usually has a few default ones).
3. Run SELECT version(); and confirm it returns your installed PostgreSQL version.
4. Run \? and skim the list, you don’t need to memorize it, just see what’s available.
5. Run \q to disconnect.

Recap

• psql -U postgres connects to a running PostgreSQL server from the command line.
• psql meta-commands (\l, \c, \dt, \d, \q) start with a backslash and don’t use a semicolon, they’re for navigating, not querying data.
• Plain SQL statements work at the same prompt, and end with a semicolon.

Next lesson: using what you now know to create your very first real database.