Make sure PostgreSQL bin is on your PATH before running these commands. If psql is not recognized, see Fix PATH issues.
Connect
Connect to PostgreSQL with psql
# Connect as postgres superuser:
C:\> psql -U postgres
Password for user postgres:
psql (18.3)
Type "help" for help.
postgres=#
# Connect to a specific database:
C:\> psql -U postgres -d mydb
# Connect to a remote server:
C:\> psql -h 192.168.1.10 -U postgres -d mydb -p 5432
Meta commands
Essential psql backslash commands
| Command | What it does |
|---|---|
\l | List all databases |
\c dbname | Connect to database |
\dt | List tables in current schema |
\dt *.* | List all tables in all schemas |
\d tablename | Describe table (columns, types, indexes) |
\du | List users and roles |
\dn | List schemas |
\di | List indexes |
\df | List functions |
\x | Toggle expanded output (wide columns) |
\i file.sql | Execute SQL from a file |
\o output.txt | Send output to file |
\timing | Toggle query execution time display |
\e | Open query in external editor |
\q | Quit psql |
Database operations
Common database operations
-- Create a database:
postgres=# CREATE DATABASE myapp;
-- Create a user with password:
postgres=# CREATE USER myuser WITH PASSWORD 'securepass';
-- Grant all privileges on database to user:
postgres=# GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;
-- Drop a database (disconnect users first):
postgres=# DROP DATABASE IF EXISTS myapp;
Table operations
Create, query and manage tables
-- Create a table:
mydb=# CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMPTZ DEFAULT NOW());
-- Insert a row:
mydb=# INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
-- Query rows:
mydb=# SELECT * FROM users LIMIT 10;
-- Describe table structure:
mydb=# \d users
Admin commands
Useful admin queries
-- Show server version:
postgres=# SELECT version();
-- Show active connections:
postgres=# SELECT pid, usename, application_name, state FROM pg_stat_activity;
-- Show database sizes:
postgres=# SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;
-- Kill a connection by PID:
postgres=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 12345;
FAQ
psql questions
How do I run a SQL file with psql?
Use
psql -U postgres -d mydb -f C:\path o\script.sql from the command line. Or inside psql: \i C:/path/to/script.sql (use forward slashes inside psql).How do I exit psql?
Type
\q and press Enter. Or press Ctrl+D.How do I change the output format?
Toggle expanded (vertical) mode with
\x. Save output to a file with \o filename.txt. For CSV output: \copy (SELECT ...) TO file.csv WITH CSV HEADER