On this page 33 sections
  1. Connect to PostgreSQL
  2. Connection information
  3. List databases
  4. Change database
  5. List schemas
  6. List tables
  7. Describe tables
  8. List columns with SQL
  9. List users and roles
  10. Create a database
  11. Delete a database
  12. Create users and roles
  13. Grant privileges
  14. Basic SELECT queries
  15. Count rows
  16. Sort query results
  17. Insert data
  18. Update data
  19. Delete data
  20. List views
  21. List indexes
  22. List functions
  23. Show PostgreSQL version
  24. Show configuration values
  25. Active connections
  26. Execute SQL from the shell
  27. Execute SQL from a file
  28. Export query results
  29. Import CSV data
  30. Dump a database
  31. Restore a database
  32. Useful psql display commands
  33. Quit psql
Lab use only. Apply these techniques only to systems you own or have explicit permission to test.
01

Connect to PostgreSQL

psql is the PostgreSQL command-line client. A connection normally specifies a user, database, host and optionally a port.

Connect locally
psql -U postgres
Connect to a specific database
psql -U postgres -d appdb
Connect to a remote server
psql -h 10.10.10.20 -p 5432 -U postgres -d appdb
Connect using a PostgreSQL URI
psql 'postgresql://postgres:password@10.10.10.20:5432/appdb'
02

Connection information

Inside psql, backslash commands are client-side meta-commands. They do not require a trailing semicolon.

Show current connection
\conninfo
Show current database
SELECT current_database();
Show current user
SELECT current_user;
03

List databases

\l lists databases available on the PostgreSQL server. \l+ includes additional information such as size and tablespace.

List databases
\l
Detailed database list
\l+
List databases with SQL
SELECT datname FROM pg_database;
04

Change database

\c reconnects the current psql session to another database. PostgreSQL does not use a SQL USE database statement like MySQL.

Connect to another database
\c appdb
Change database and user
\c appdb appuser
05

List schemas

Schemas organize database objects such as tables, views and functions. The public schema exists by default in many PostgreSQL databases.

List schemas
\dn
Detailed schema list
\dn+
Show search path
SHOW search_path;
06

List tables

\dt lists tables visible in the current search path. A schema can be specified explicitly when needed.

List tables
\dt
List all tables in all schemas
\dt *.*
List tables from a schema
\dt public.*
Detailed table list
\dt+
07

Describe tables

\d shows the structure of database objects. For a table it displays columns, data types, indexes and constraints.

Describe table
\d users
Detailed table information
\d+ users
Describe schema-qualified table
\d public.users
08

List columns with SQL

information_schema provides portable metadata about database objects.

Show table columns
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'users';
09

List users and roles

PostgreSQL uses roles for both users and groups. A role with LOGIN permission can authenticate to the server.

List roles
\du
Detailed role list
\du+
List roles with SQL
SELECT rolname FROM pg_roles;
10

Create a database

CREATE DATABASE creates a new PostgreSQL database. The connected role must have sufficient privileges.

Create database
CREATE DATABASE appdb;
Create database from shell
createdb -U postgres appdb
11

Delete a database

DROP DATABASE permanently removes a database and its contents.

Drop database
DROP DATABASE appdb;
Drop database from shell
dropdb -U postgres appdb
12

Create users and roles

CREATE ROLE creates a PostgreSQL role. LOGIN allows the role to authenticate like a normal database user.

Create login user
CREATE ROLE appuser WITH LOGIN PASSWORD 'StrongPassword';
Create user syntax
CREATE USER appuser WITH PASSWORD 'StrongPassword';
Change password
ALTER USER appuser WITH PASSWORD 'NewPassword';
13

Grant privileges

Privileges control which roles can connect to databases and access database objects.

Allow database connection
GRANT CONNECT ON DATABASE appdb TO appuser;
Grant schema usage
GRANT USAGE ON SCHEMA public TO appuser;
Grant table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
Grant all database privileges
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
14

Basic SELECT queries

SELECT retrieves data from tables. LIMIT is useful when inspecting an unfamiliar database without returning large result sets.

Read all rows
SELECT * FROM users;
Limit results
SELECT * FROM users LIMIT 10;
Select specific columns
SELECT id, username, email FROM users;
Filter rows
SELECT * FROM users WHERE username = 'admin';
15

Count rows

COUNT is useful for quickly checking how much data exists in a table.

Count all rows
SELECT COUNT(*) FROM users;
Count filtered rows
SELECT COUNT(*) FROM users WHERE active = true;
16

Sort query results

ORDER BY sorts result rows using one or more columns.

Sort newest first
SELECT * FROM users ORDER BY id DESC;
Sort ascending
SELECT * FROM users ORDER BY username ASC;
17

Insert data

INSERT adds new rows to a table.

Insert row
INSERT INTO users (username, email)
VALUES ('samir', 'samir@example.com');
Insert and return created row
INSERT INTO users (username, email)
VALUES ('samir', 'samir@example.com')
RETURNING *;
18

Update data

UPDATE modifies existing rows. A WHERE clause should normally be used to avoid changing every row in the table.

Update row
UPDATE users
SET email = 'new@example.com'
WHERE username = 'samir';
19

Delete data

DELETE removes rows matching the specified condition.

Delete row
DELETE FROM users WHERE username = 'samir';
20

List views

Views are stored queries that can be queried similarly to tables.

List views
\dv
Detailed view list
\dv+
21

List indexes

Indexes can improve query performance and are often useful when reviewing a database schema.

List indexes
\di
List indexes for all schemas
\di *.*
22

List functions

\df shows functions available in the current database.

List functions
\df
Detailed function list
\df+
23

Show PostgreSQL version

The server version can be checked using SQL or psql itself.

Server version
SELECT version();
psql client version
psql --version
24

Show configuration values

SHOW displays the current value of PostgreSQL runtime configuration parameters.

Show listening port
SHOW port;
Show data directory
SHOW data_directory;
Show configuration file
SHOW config_file;
Show authentication file
SHOW hba_file;
25

Active connections

pg_stat_activity provides information about current PostgreSQL sessions and queries.

Show active sessions
SELECT pid, usename, datname, client_addr, state
FROM pg_stat_activity;
Show running queries
SELECT pid, usename, datname, query
FROM pg_stat_activity
WHERE state = 'active';
26

Execute SQL from the shell

-c executes a SQL statement directly without entering an interactive psql session.

Run single query
psql -U postgres -d appdb -c "SELECT current_database();"
List tables from shell
psql -U postgres -d appdb -c "\dt"
27

Execute SQL from a file

SQL scripts can be executed either from the shell or from inside an existing psql session.

Execute SQL file from shell
psql -U postgres -d appdb -f backup.sql
Execute SQL file inside psql
\i script.sql
28

Export query results

\copy runs through the client and is convenient for exporting query results to CSV files.

Export table to CSV
\copy users TO 'users.csv' CSV HEADER
Export query to CSV
\copy (SELECT id, username FROM users) TO 'users.csv' CSV HEADER
29

Import CSV data

\copy can also load local CSV data into a table.

Import CSV
\copy users(id, username, email) FROM 'users.csv' CSV HEADER
30

Dump a database

pg_dump creates a logical backup of a PostgreSQL database.

SQL backup
pg_dump -U postgres -d appdb > appdb.sql
Custom-format backup
pg_dump -U postgres -d appdb -Fc -f appdb.dump
31

Restore a database

Plain SQL dumps can be restored with psql. Custom-format dumps are normally restored with pg_restore.

Restore SQL backup
psql -U postgres -d appdb < appdb.sql
Restore custom-format backup
pg_restore -U postgres -d appdb appdb.dump
32

Useful psql display commands

psql includes several commands that make query output easier to inspect in a terminal.

Toggle expanded output
\x
Show query history
\s
Show psql help
\?
Show SQL command help
\h SELECT
33

Quit psql

\q closes the current psql session.

Exit
\q