PostgreSQL psql Commands Cheat Sheet for Beginners

Quick answer

The psql commands you’ll probably reach for most are psql -U username -d database to connect, \l to list databases, \c database to switch databases, \dt to list tables, \d table_name to describe a table, \i file.sql to run a SQL file, and \q to leave psql.

psql can feel a little odd at first. Part of the time you’re writing normal SQL, and part of the time you’re using psql’s own backslash commands. This cheat sheet keeps the everyday stuff close by, so you can connect, inspect, query, import, and export without having to pull up the docs every time.

What psql is

psql is PostgreSQL’s official command-line client. You use it to connect to a PostgreSQL server, run SQL queries, manage databases, inspect schemas, execute scripts, and troubleshoot database access.

Inside psql, you’ll deal with two kinds of commands:

• SQL commands, such as SELECT * FROM users;. These usually end with a semicolon.
• psql meta-commands, such as \dt or \l. These start with a backslash and don’t need a semicolon.

Connect to PostgreSQL from the command line

Example:

psql -h localhost -p 5432 -U postgres -d appdb

If password authentication is turned on, psql asks for the password after you run the command.

psql commands you’ll use often

Database and connection commands

These are the commands to use when you need to check where you’re connected, or move to another database.

\conninfo

Shows the current database, user, host, and port.

\l

Lists all databases on the PostgreSQL server.

\c appdb

Connects to the appdb database.

\c appdb appuser

Connects to appdb as the user appuser.

Inspect tables, schemas, and objects

These commands are handy when you’ve inherited a database or just need to see its structure without guessing.

Example:

\d+ orders

That displays columns, data types, indexes, constraints, storage details, and the table description when one is available.

Run SQL queries in psql

Normal SQL works directly inside psql. Just remember the semicolon at the end.

SELECT current_database();

SELECT id, email, created_at FROM users LIMIT 10;

SELECT COUNT(*) FROM orders;

If your prompt changes to something like appdb-#, psql is probably waiting for you to finish a multi-line SQL statement. Add the missing semicolon, or cancel the command with Ctrl+C.

Create and manage databases

You can create databases with SQL from inside psql:

CREATE DATABASE appdb;

List databases:

\l

Connect to the new database:

\c appdb

Drop a database only when you’re sure it’s no longer needed:

DROP DATABASE olddb;

Be careful: PostgreSQL won’t let you drop the database you’re currently connected to. Connect to another database first, such as postgres, then run the drop command.

Import and run SQL files

To run a SQL file from inside psql, use \i followed by the file path.

\i /home/user/schema.sql

You can run a SQL file from your shell too, without opening the interactive psql prompt first:

psql -U postgres -d appdb -f schema.sql

This is a common way to handle schema setup, migrations, seed data, and database restore tasks.

Export query results

For quick exports, use \copy. It runs from the client side, which is often easier than server-side COPY.

\copy users TO 'users.csv' CSV HEADER

Export selected columns:

\copy (SELECT id, email FROM users) TO 'users_export.csv' CSV HEADER

Import a CSV file:

\copy users(email, name) FROM 'users.csv' CSV HEADER

Make output easier to read

When a table has lots of columns, the normal output can get messy. Expanded mode helps.

\x

Run your query again:

SELECT * FROM users LIMIT 1;

Turn on query timing:

\timing

Send output to a file:

\o results.txt

Send output back to the terminal:

\o

Role and permission commands

Common psql mistakes

• Forgetting the semicolon: SQL commands need ;. psql backslash commands don’t.
• Using psql commands outside psql: Commands like \dt only work inside the psql prompt.
• Connecting to the wrong database: Run \conninfo before making changes.
• Confusing schemas with databases: A database can contain multiple schemas. Tables are usually inside the public schema unless configured differently.
• Dropping objects too quickly: Always verify the current connection and object name first.

Good habits in psql

• Use \conninfo before running destructive commands.
• Use \d+ table_name when checking indexes, constraints, and storage details.
• Keep migration and setup scripts in version control.
• Use \timing when comparing query performance.
• Prefer \copy for local CSV imports and exports.
• Don’t put passwords directly in shell history when you’re working on shared systems.

A basic psql workflow

psql -U postgres -d appdb
\conninfo
\dt
\d users
SELECT COUNT(*) FROM users;
\timing
\x
SELECT * FROM users LIMIT 1;
\q

This little workflow covers the basics: connect, confirm the database, list tables, inspect a table, run a query, adjust output, and exit safely.