The psql CLI

psql is PostgreSQL’s official interactive command-line client. It ships with every PostgreSQL installation and is the fastest way to run SQL, inspect your schema, and manage a database once you are comfortable with the terminal. Most experienced PostgreSQL users reach for psql before opening any graphical tool.

Connecting

Connecting with a username and database

Bash

psql -U username -d dbname

If you omit -d, psql connects to a database with the same name as your username by default. You can also pass -h for a host and -p for a port when connecting to a remote server:

Bash

psql -h db.example.com -p 5432 -U app_user -d app_production

Once connected, the prompt changes to show the database you are currently in, for example dbname=>, or dbname=# if you are connected as a superuser.

Essential meta-commands

Commands that start with a backslash are meta-commands — they are handled by psql itself, not sent to the server as SQL. They are how you explore a database interactively.

Command	Purpose
\l	List all databases on the server
\c dbname	Connect to a different database
\dt	List tables in the current schema
\d tablename	Describe a table — columns, types, indexes, constraints
\du	List roles (users) and their privileges
\dn	List schemas in the current database
?	Show help for all meta-commands
\q	Quit psql

Running SQL

Anything that is not a backslash command is treated as SQL and sent to the server. Statements must end with a semicolon:

SQL

SELECT current_database(), current_user, now();

Running a script file

Rather than typing statements one at a time, you can execute an entire .sql file with \i:

Bash

\i filename.sql

This is exactly how you will set up the sample database used throughout this series — save the setup script to a file and load it with a single \i command.

Command-line vs interactive mode

You can also run a single statement without entering the interactive shell at all, which is handy in scripts:

Bash

psql -U postgres -d dbname -c "SELECT count(*) FROM orders;"

Tip

If a query returns a table too wide to read comfortably, toggle \x to switch into expanded (vertical) output — each column is printed on its own line instead of being squeezed into a row. Run \x again to switch back.