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.
psql commands cheat sheet for beginners: learn how to connect, list databases and tables, run queries, and quit psql with simple, copy-paste examples.
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.
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.
Open your terminal and type the command below. It connects to a database called postgres as a user called postgres.
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.
This is the most important idea for beginners. Mixing them up causes most early confusion.
| Type | Looks like | Needs a semicolon? | Example |
|---|---|---|---|
| SQL command | SELECT * FROM users; | Yes | SELECT 1; |
| psql meta-command | \dt | No | \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.
\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.
\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.
| Command | What it does |
|---|---|
| \dt | List tables |
| \dv | List views (saved queries) |
| \di | List indexes |
| \du | List users (called roles) |
| \dn | List schemas (folders for tables) |
| \df | List functions |
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.
\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.
\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.
\?
\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.
New to PostgreSQL? Start with our step-by-step install guide, then practice these commands.
Read the install guideRepeat this a few times and the commands will become habit. Within a day you will not need the cheat sheet.
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.
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.
Type \q and press Enter. You can also press Ctrl+D. Both return you to your normal terminal.
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.
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 FepiqOccasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.
Keep reading
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
Learn JavaScript template literals with simple examples: backticks, ${} variables, and multi-line strings. A beginner-friendly guide you can copy and run.
Let's build something
Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.