psql is the command-line tool that comes with PostgreSQL. You type a command, and the server answers in plain text right below it. In this part you connect to your server with psql, run your first SQL statement, and learn the few psql commands you’ll use in every part that follows.
This is Part 2 of the PostgreSQL Tutorial. The parts in Module A, Start Here:
- Part 1: What Is PostgreSQL? Databases, Tables and SQL Explained
- Part 2: Connect with psql and Run Your First Commands (you are here)
- Part 3: pgAdmin 4 for Beginners
All parts of the PostgreSQL Tutorial
Module A: Start Here
- Part 1: What Is PostgreSQL? Databases, Tables and SQL Explained
- Part 2: Connect with psql and Run Your First Commands
- Part 3: pgAdmin 4 for Beginners
Module B: Build the Database
- Part 4: Create a Database
- Part 5: Data Types Explained
- Part 6: Create Tables
- Part 7: Primary Keys, Foreign Keys and Constraints
- Part 8: Insert, Update and Delete Data
- Part 9: Import and Export CSV Files
Module C: Query Data
- Part 10: SELECT, WHERE, ORDER BY and LIMIT
- Part 11: LIKE, IN, BETWEEN, NULL and CASE
- Part 12: GROUP BY and Aggregate Functions
- Part 13: JOINs Explained
- Part 14: Subqueries and CTEs
- Part 15: UNION, INTERSECT and EXCEPT
- Part 16: String, Number and Conversion Functions
- Part 17: Dates, Times and Time Zones
Module D: Design and Performance
- Part 18: Database Design and Normalization
- Part 19: Views and Materialized Views
- Part 20: Indexes Explained
- Part 21: EXPLAIN and Query Performance
- Part 22: Transactions, Isolation Levels and Locks
Module E: Advanced SQL and Programming
- Part 23: Window Functions
- Part 24: Recursive Queries and LATERAL Joins
- Part 25: JSON and JSONB
- Part 26: Arrays, Enums, Ranges and Generated Columns
- Part 27: Full-Text Search
- Part 28: Functions and Stored Procedures
- Part 29: Triggers
Module F: Administration and Security
- Part 30: Users, Roles and Privileges
- Part 31: Row-Level Security
- Part 32: Configuration and Restarting the Server
- Part 33: Allow Remote Connections Securely
- Part 34: Backup and Restore
- Part 35: Automate Backups
- Part 36: VACUUM, ANALYZE and Autovacuum
- Part 37: Monitoring and Slow Queries
- Part 38: Table Partitioning
- Part 39: Streaming Replication
- Part 40: Upgrade to a New Major Version
Module G: PostgreSQL for Developers
- Part 41: Connect from C# .NET with Npgsql
- Part 42: Entity Framework Core with PostgreSQL
- Part 43: Connect from Python with psycopg
- Part 44: Connect from Node.js with node-postgres
Module H: Extensions, AI and Cloud
- Part 45: Extensions
- Part 46: Vector Search with pgvector
- Part 47: Azure Database for PostgreSQL
- Part 48: Migrate to Azure and What to Learn Next
Every command and output on this page is real, run on PostgreSQL 19 on macOS and on Ubuntu.
Where the bookstore database is now: it doesn’t exist yet. Part 4 creates it, so there’s nothing to catch up on. In this part you only talk to the server.
Prerequisites
- Part 1: What Is PostgreSQL?, which explains the server, the client and the database this part talks about.
- PostgreSQL 19 installed and running on your computer.
Step 1: Connect to PostgreSQL with psql
psql is a client: a program that connects to the PostgreSQL server and sends it what you type. How you start it depends on your system.
On a Mac
Open Terminal (press Command-Space, type Terminal and press Return), then run the command below. If PostgreSQL isn’t installed yet, follow How to Install PostgreSQL 19 on macOS first.
Terminal
psql -U postgres
-U postgres connects as postgres, the administrator account the installer created. Type the password you chose during the install and press Return. Nothing appears while you type, which is normal.
Output
Password for user postgres:
psql (19.0)
Type "help" for help.
postgres=#
If Terminal says zsh: command not found: psql, PostgreSQL’s folder, /Library/PostgreSQL/19/bin, isn’t on your PATH yet. How to Add a Folder to PATH on macOS (zsh) shows how to add it.
On Ubuntu
Open a terminal and run the command below. If PostgreSQL isn’t installed yet, follow How to Install PostgreSQL 19 on Ubuntu first.
Ubuntu Terminal
sudo -u postgres psql
On Ubuntu, the install created a Linux user named postgres, and PostgreSQL lets that user in without a password. sudo -u postgres runs psql as that user, as How to Run a Command as Another User on Ubuntu explains.
Output
psql (19.0 (Ubuntu 19.0-2.pgdg26.04+1))
Type "help" for help.
postgres=#
The psql prompt
postgres=# is psql’s prompt: psql is waiting for you to type. postgres is the database you’re connected to, a default one every server has. The # means you’re connected as a superuser, an account that’s allowed to do anything. Other users see > instead.
Step 2: Run Your First SQL Statement
Type this at the postgres=# prompt and press Return:
postgres=#
SELECT version();
Output
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 19.0 on aarch64-apple-darwin24.6.0, compiled by Apple clang version 17.0.0 (clang-1700.0.13.5), 64-bit
(1 row)
SELECT asks PostgreSQL for something, version() is a built-in function that returns the server’s version, and the semicolon ends the statement. The answer always comes back as a small table: the column name, a line, the values, and the number of rows. On Ubuntu the same statement names Ubuntu and its compiler:
Output
version
------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 19.0 (Ubuntu 19.0-2.pgdg26.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit
(1 row)
Forgot the semicolon?
psql sends a statement to the server only when it sees a semicolon. Press Return without one, and the prompt changes from =# to -#: psql is waiting for the rest of the statement. Type the semicolon and press Return, and the answer appears:
psql session
postgres=# SELECT version()
postgres-# ;
Step 3: List Your Databases
Commands that start with a backslash are psql’s own commands, not SQL. psql handles them itself, and they don’t need a semicolon. \l lists the databases on the server:
postgres=#
\l
Output
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | C | C | | |
template0 | postgres | UTF8 | libc | C | C | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | C | C | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
A new server has three databases. postgres is the default one you’re connected to. template0 and template1 are templates that PostgreSQL copies whenever you create a database. Part 4 creates your first one. On Ubuntu, the Collate and Ctype columns show C.UTF-8 instead of C: they’re language settings, and they can differ from one computer to another.
Step 4: Check Where You’re Connected
\conninfo shows which database you’re in, which user you are, and how you connected:
postgres=#
\conninfo
Output
Connection Information
Parameter | Value
----------------------+----------
Database | postgres
Client User | postgres
Socket Directory | /tmp
Server Port | 5432
Options |
Protocol Version | 3.0
Password Used | true
GSSAPI Authenticated | false
Backend PID | 71203
SSL Connection | false
Superuser | on
Hot Standby | off
(12 rows)
The rows that matter now are Database, Client User, Server Port and Superuser. Server Port 5432 is PostgreSQL’s standard port. On Ubuntu, Socket Directory is /var/run/postgresql and Password Used is false, because the postgres Linux user gets in without one.
Step 5: Get Help Inside psql
psql’s welcome line says Type "help" for help. Here’s what that shows:
postgres=#
help
Output
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
\? lists every psql command, grouped by topic. These are the first lines:
postgres=#
\?
Output (the first lines)
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display result in crosstab
\errverbose show most recent error message at maximum verbosity
\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);
\g with no arguments is equivalent to a semicolon
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q quit psql
The list is longer than your window, so psql shows one screenful at a time with a : on the last line. Press Space for the next screenful, or q to go back to the prompt.
\h shows the syntax of a SQL command. Give it the command’s name:
postgres=#
\h SELECT
Output (the lines that matter)
Command: SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
…
URL: https://www.postgresql.org/docs/19/sql-select.html
The last line links the matching page of the PostgreSQL documentation, which explains every part of the syntax.
Step 6: Quit psql
postgres=#
\q
\q closes psql and returns you to the Terminal prompt. Since PostgreSQL 11, typing exit or quit on an empty line does the same, and so does Control-D.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Typing SQL in Terminal before starting psql | zsh doesn’t understand SQL and waits at a function> prompt | Press Control-C, start psql, then type the SQL at the postgres=# prompt. |
Typing a psql command such as \l in Terminal | zsh: command not found: l | Start psql first. Backslash commands only work inside psql. |
| Pressing Return without the semicolon | The prompt changes to postgres-# and nothing runs | Type ; and press Return. |
| Typing your Mac password instead of the postgres password | psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: password authentication failed for user "postgres" | Type the password you chose for postgres during the install. |
On Ubuntu, running psql without sudo -u postgres | psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "ubuntu" does not exist | Run sudo -u postgres psql. |
Troubleshooting
| Problem | Cause | Fix |
|---|---|---|
zsh: command not found: psql on a Mac | PostgreSQL’s folder isn’t on your PATH | Add /Library/PostgreSQL/19/bin to PATH, as How to Add a Folder to PATH on macOS (zsh) shows, then open a new Terminal window. |
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory, then Is the server running locally and accepting connections on that socket? | The PostgreSQL server isn’t running | On Ubuntu, run sudo systemctl start postgresql. On a Mac, restart the Mac: the installer set the server to start with it. |
The output stops with a : on the last line | The output is longer than your window, so psql shows it one screenful at a time | Press Space for more, or q to go back to the prompt. |
FAQ
What is psql?
The command-line client that comes with PostgreSQL. It connects to a PostgreSQL server, sends it the SQL you type and prints the answers. It also has its own backslash commands for jobs like listing databases.
Is psql the same on a Mac and on Ubuntu?
Yes. Once you’re connected, every command in this part works the same on both. Only the way you start psql differs: with your password on a Mac, and as the postgres user on Ubuntu.
Do SQL keywords have to be in capitals?
No. PostgreSQL reads SELECT and select the same way, as the SQL syntax chapter of the documentation explains. Writing keywords in capitals is a habit that makes SQL easier to read, and this series follows it.
Why does the prompt say postgres?
Because you’re connected to the database named postgres. When you connect to another database, the prompt shows its name instead, as Part 4 shows.
Conclusion
You connected to PostgreSQL with psql, ran your first SQL statement, and used psql’s own commands to list the databases, check your connection, get help and quit. Next, Part 3 shows pgAdmin, the graphical tool that does the same things in windows and lists instead of text.
← Previous: Part 1: What Is PostgreSQL? Databases, Tables and SQL Explained
Coming next: Part 3: pgAdmin 4 for Beginners.
Additional Resources
- PostgreSQL 19 documentation: psql
- PostgreSQL 19 documentation: Accessing a Database
- PostgreSQL 19 documentation: SELECT
- PostgreSQL 19 documentation: System Information Functions
- PostgreSQL Tutorial for Beginners: every part and setup guide
Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.
