All posts
PostgreSQLNovember 8, 20267 min read

psql Commands Cheat Sheet for Beginners (PostgreSQL)

psql commands cheat sheet for beginners: learn how to connect, list databases and tables, run queries, and quit psql with simple, copy-paste examples.

F
Fepiq Team
Fepiq

Looking for a psql commands cheat sheet for beginners? Here is the short answer. psql is the text tool that lets you talk to a PostgreSQL database. It has two kinds of commands: SQL commands that end with a semicolon, and special psql commands that start with a backslash, like \l (list databases) and \dt (list tables). This guide teaches the ones you will use every day.

You do not need any experience. We define every term when it first appears, and every example is small enough to copy and run. If you have not installed PostgreSQL yet, read our install guide first, then come back.

What is psql?

PostgreSQL is a database, which is a program that stores data in organized tables. psql is the command-line client for PostgreSQL. A command line is a screen where you type text commands instead of clicking buttons. With psql you can look around your database, run queries, and fix mistakes quickly.

How to start psql and connect

Open your terminal and type the command below. It connects to a database called postgres as a user called postgres.

bash
psql -U postgres -d postgres

# Full form with host and port
psql -h localhost -p 5432 -U postgres -d postgres

-U sets the user name. -d sets the database name. -h is the host, which is the computer where the database runs (localhost means your own computer). -p is the port, a number that identifies the PostgreSQL service; 5432 is the default. psql asks for your password, then shows a prompt like postgres=#. That prompt means you are connected.

Two kinds of commands

This is the most important idea for beginners. Mixing them up causes most early confusion.

TypeLooks likeNeeds a semicolon?Example
SQL commandSELECT * FROM users;YesSELECT 1;
psql meta-command\dtNo\l

SQL commands talk to the database. Meta-commands (also called backslash commands) talk to psql itself. If you forget the semicolon on an SQL command, psql waits for more input and shows a prompt like postgres-#. Type a semicolon and press Enter to finish.

List and switch databases

bash
\l
\c shop_db
\conninfo

\l lists all databases on the server. \c shop_db connects to a database named shop_db (the c stands for connect). \conninfo tells you which database, user, and port you are using right now. Use it whenever you are not sure where you are.

List and inspect tables

bash
\dt
\d users
\d+ users

\dt lists the tables in the current database. \d users shows the structure of a table called users: its columns, data types, and rules. \d+ shows the same with extra details such as storage size. Use \d whenever you forget a column name.

Other useful list commands

CommandWhat it does
\dtList tables
\dvList views (saved queries)
\diList indexes
\duList users (called roles)
\dnList schemas (folders for tables)
\dfList functions

Run a query and read the result

sql
SELECT id, name, email
FROM users
LIMIT 5;

SELECT picks the columns you want. FROM says which table to read. LIMIT 5 returns only the first five rows. The semicolon at the end tells psql the command is complete. If the output is wide and hard to read, try the next trick.

Make wide results easier to read

bash
\x
SELECT * FROM users LIMIT 1;
\x

\x turns on expanded display. Each row is shown as a vertical list of column names and values instead of a wide table. Typing \x again turns it off. It works like a light switch.

Run SQL from a file

bash
\i setup.sql

\o results.txt
SELECT * FROM users;
\o

\i setup.sql runs every SQL command saved in the file setup.sql, which is handy for creating tables again. \o results.txt sends query output into a file instead of the screen. A plain \o on its own switches output back to the screen.

Get help and quit

bash
\?
\h CREATE TABLE
\q

\? lists all psql backslash commands. \h CREATE TABLE shows help for an SQL command. \q quits psql and returns you to your normal terminal. You can also press Ctrl+D.

Common beginner mistakes

  • Adding a semicolon after a backslash command. It is not needed.
  • Forgetting the semicolon after SQL. psql will just wait.
  • Running commands in the wrong database. Check with \conninfo.
  • Typing a table name with capital letters. PostgreSQL usually stores names in lowercase.
  • Closing the terminal without \q. It is safe, but \q is cleaner.

New to PostgreSQL? Start with our step-by-step install guide, then practice these commands.

Read the install guide

Quick practice plan

  1. Start psql with psql -U postgres.
  2. Run \l to see your databases.
  3. Connect to one with \c name.
  4. Run \dt to list its tables.
  5. Run \d tablename to see the columns.
  6. Run a small SELECT ... LIMIT 5; query.
  7. Type \q to exit.

Repeat this a few times and the commands will become habit. Within a day you will not need the cheat sheet.

Frequently asked questions

What is psql used for?+

psql is the official command-line tool for PostgreSQL. You use it to connect to a database, run SQL queries, and inspect tables. It is lightweight and comes with PostgreSQL.

How do I list all tables in psql?+

Connect to your database and type \dt, then press Enter. It shows every table in the current schema. Use \d tablename to see a table's columns.

How do I exit psql?+

Type \q and press Enter. You can also press Ctrl+D. Both return you to your normal terminal.

Why does psql not run my SQL command?+

Most often you forgot the semicolon at the end. psql then waits for more input and shows a prompt with a dash, like postgres-#. Type ; and press Enter to run it.

Is psql the same as PostgreSQL?+

No. PostgreSQL is the database server that stores your data. psql is just one client program that connects to it. Other clients include pgAdmin and code libraries.

Want help building a database-backed app or website? Our team can help you from idea to launch.

Talk to Fepiq

Get new posts in your inbox

Occasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.

Keep reading

Related posts

All posts

Let's build something

Ready to ship your next product with Fepiq?

Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.