PostgreSQL CLI

psql cheatsheet — connect, query, manage PostgreSQL. \l list dbs, \dt tables, \d table describe, \copy, pg_dump. Full PostgreSQL CLI reference with meta-commands.

6 min read

What it is

psql is the interactive terminal for PostgreSQL, used for running SQL commands, managing databases, and inspecting server state.

Installation

Linux (Debian/Ubuntu):

sudo apt update
sudo apt install postgresql-client

Linux (Fedora/CentOS/RHEL):

sudo dnf install postgresql
# or
sudo yum install postgresql

macOS (Homebrew):

brew update
brew install postgresql

Windows: Download the installer from the PostgreSQL website and follow the instructions. The psql executable will be added to your system’s PATH.

Core Concepts

Database: A collection of schemas.
Schema: A namespace within a database that contains tables, functions, and other objects. The public schema is the default.
Table: A collection of related data held in rows and columns.
Meta-commands: Commands starting with a backslash (\) that psql interprets directly, not as SQL. They are used for interacting with psql itself and the PostgreSQL server.

Commands / Usage

Connecting to a Database

Connect to the default database (postgres) as the default user (postgres):
```
psql
```
Connects to postgres://postgres@localhost:5432/postgres
Connect to a specific database as the default user:
```
psql mydatabase
```
Connects to postgres://postgres@localhost:5432/mydatabase
Connect as a specific user to a specific database:
```
psql -U myuser -d mydatabase
```
Connects to postgres://myuser@localhost:5432/mydatabase
Connect to a specific host and port:
```
psql -h db.example.com -p 5433 -U myuser -d mydatabase
```
Connects to postgres://myuser@db.example.com:5433/mydatabase
Connect using a connection string (URI):
```
psql postgresql://myuser:mypassword@db.example.com:5432/mydatabase
```
Connects to the specified database using the provided credentials.

Executing SQL Commands

Execute a single SQL command and exit:
```
psql -c "SELECT version();"
```
Prints the PostgreSQL version and exits.
Execute multiple SQL commands from a file:
```
psql -f /path/to/your/script.sql
```
Runs all SQL commands found in script.sql.
Execute SQL commands interactively (default):
```
psql mydatabase
```
Opens an interactive prompt where you can type SQL commands.

Inspecting Database Objects

List all databases:
```
\l
```
Lists all databases on the server.
List all schemas in the current database:
```
\dn
```
Lists all schemas in the connected database.
List all tables in the current database:
```
\dt
```
Lists tables in the connected database.
List all tables in a specific schema:
```
\dt pg_catalog.*
```
Lists tables in the pg_catalog schema.
List all tables, views, and sequences:
```
\dt+
\dv+
\ds+
```
Lists tables, views, or sequences with additional information (size, description).
Describe a table’s columns and types:
```
\d mytable
```
Shows the columns, data types, and constraints of mytable.
Describe a table’s columns, types, and indices:
```
\d+ mytable
```
Shows detailed information about mytable, including indices and storage parameters.
List all functions:
```
\df
```
Lists all functions.
List all views:
```
\dv
```
Lists all views.
List all sequences:
```
\ds
```
Lists all sequences.
List all extensions:
```
\dx
```
Lists all installed extensions.
List all users (roles):
```
\du
```
Lists all roles and their privileges.

Controlling `psql` Behavior

Set the output format to aligned table (default):
```
psql --pset=format=aligned
```
Set the output format to unaligned (CSV-like):
```
psql --pset=format=unaligned
```
Set the output format to HTML:
```
psql --pset=format=html
```
Set the output format to LaTeX:
```
psql --pset=format=latex
```
Turn off the header in query results:
```
psql --pset=no_header
```
Turn off the footer (row count) in query results:
```
psql --pset=no_footer
```
Set the timing of commands to be shown:
```
psql --pset=timing=on
```
Set the prompt:
```
psql --set prompt1='%n@%m:%5/ %x> '
```
Sets the primary prompt to show user, host, database, and transaction status.
Ignore case for meta-commands:
```
psql --set IGNORECASE=on
```

Turn on automatic saving of command history:

psql --set HISTSIZE=1000 --set HISTFILE=~/.psql_history

Execute commands from standard input without entering interactive mode:
```
echo "SELECT * FROM users;" | psql -d mydatabase
```

Other Useful Meta-Commands

Show the current connection information:
```
\conninfo
```
Show the current database and user:
```
\c
```
(Without arguments, shows current connection info. With arguments, switches connection.)
Switch to a different database:
```
\c newdatabase
```
Switch to a different user and database:
```
\c -U newuser -d newdatabase
```
Show the contents of a file:
```
\i /path/to/another/script.sql
```
(Similar to -f, but can be used within an interactive session.)
Edit the current query buffer with an external editor:
```
\e
```
Opens the current query in your default editor ($EDITOR environment variable).
Show the last executed query:
```
\qhistory
```
Show help on meta-commands:
```
\?
```
Show help on SQL commands:
```
\h SELECT
```
Exit psql:
```
\q
```

Common Patterns

Exporting data to CSV:

psql -d mydatabase -U myuser -c "COPY mytable TO STDOUT WITH CSV HEADER;" > mytable_export.csv

Exports mytable to mytable_export.csv in CSV format with headers.

Importing data from CSV:

psql -d mydatabase -U myuser -c "COPY mytable FROM STDIN WITH CSV HEADER;" < mytable_import.csv

Imports data from mytable_import.csv into mytable.

Running a script and exiting:
```
psql -h localhost -U postgres -d mydatabase -f /path/to/setup.sql
```
Connects to mydatabase and executes all commands in setup.sql.
Piping SQL query output directly to another command:
```
psql -d mydatabase -t -c "SELECT email FROM users WHERE active = TRUE;" | grep "@example.com"
```
Gets active user emails and filters for those with @example.com. -t turns off headers and footers for cleaner output.
Executing a query and getting just the value:
```
psql -d mydatabase -t -A -c "SELECT COUNT(*) FROM orders WHERE status = 'pending';"
```
Returns only the count, without any formatting. -A is for unaligned output.

Checking the PostgreSQL version on a remote server:

psql -h db.example.com -U myuser -c "SELECT version();"

Creating a new database:

psql -U postgres -c "CREATE DATABASE new_db_name;"

Dropping a database:

psql -U postgres -c "DROP DATABASE old_db_name;"

Gotchas

Meta-commands vs. SQL: Remember that commands starting with \ are psql meta-commands and are not sent to the server as SQL. SQL commands must end with a semicolon (;) for psql to execute them.
Quoting: When passing arguments to meta-commands (like table names or schema names), they often need to be quoted if they contain spaces or are keywords. Double quotes (") are standard SQL quoting.
Connection Defaults: If you don’t specify a host, port, user, or database, psql uses defaults: localhost, 5432, the current OS user, and a database with the same name as the current OS user.
COPY Permissions: The COPY command (used for import/export) typically requires superuser privileges or membership in the pg_read_server_files or pg_write_server_files roles for server-side file operations. For client-side COPY ... TO STDOUT or COPY ... FROM STDIN, these restrictions are usually relaxed.
\i vs. -f: -f executes a script file when psql starts. \i executes a script file from within an active psql session.
Encoding Issues: If you encounter garbled text, ensure your client’s locale and psql’s encoding settings match the database encoding. You can set PGCLIENTENCODING=UTF8 or use psql --set client_encoding=UTF8.