Skip to main content
0 reactions

PostgreSQL Tutorial — Part 2: Connect with psql and Run Your First Commands

Featured card for PostgreSQL Tutorial Part 2, titled Connect with psql, with a terminal window listing four ticked commands: psql -U postgres, SELECT version();, \l and \q.
Part 2: connect to PostgreSQL with psql and run your first commands.

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:

All parts of the PostgreSQL Tutorial

Module A: Start Here

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.

TerminalBash
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.

OutputPlain text
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 TerminalBash
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.

OutputPlain text
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=#SQL
SELECT version();
OutputPlain text
                                                      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:

OutputPlain text
                                                              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 sessionPlain text
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=#Plain text
\l
OutputPlain text
                                                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=#Plain text
\conninfo
OutputPlain text
     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=#Plain text
help
OutputPlain text
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=#Plain text
\?
Output (the first lines)Plain text
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=#Plain text
\h SELECT
Output (the lines that matter)Plain text
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=#Plain text
\q

\q closes psql and returns you to the Terminal prompt. Since PostgreSQL 11 (opens in a new tab), typing exit or quit on an empty line does the same, and so does Control-D.

Common Mistakes

MistakeWhat happensDo this instead
Typing SQL in Terminal before starting psqlzsh doesn’t understand SQL and waits at a function> promptPress Control-C, start psql, then type the SQL at the postgres=# prompt.
Typing a psql command such as \l in Terminalzsh: command not found: lStart psql first. Backslash commands only work inside psql.
Pressing Return without the semicolonThe prompt changes to postgres-# and nothing runsType ; and press Return.
Typing your Mac password instead of the postgres passwordpsql: 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 postgrespsql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "ubuntu" does not existRun sudo -u postgres psql.

Troubleshooting

ProblemCauseFix
zsh: command not found: psql on a MacPostgreSQL’s folder isn’t on your PATHAdd /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 runningOn 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 lineThe output is longer than your window, so psql shows it one screenful at a timePress 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 (opens in a new tab) 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

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

0 comments