# psql — The Interactive PostgreSQL Client

> Practical guide to psql, the interactive PostgreSQL client: connect, run queries and manage schemas with backslash meta-commands.

Source: https://www.jpkc.com/db/en/cheatsheets/build-languages/psql/

<!-- PROSE:intro -->
psql is PostgreSQL's interactive terminal: it's where you connect to a database, run SQL queries and drive the client through meta-commands that all start with a backslash – such as `\l` for every database, `\dt` for the tables or `\d` to describe one. Beyond querying, it doubles as an administration tool: create roles, grant privileges, back up and restore databases. This guide collects the commands you reach for most in day-to-day work, from opening a connection to exporting CSV.
<!-- PROSE:intro:end -->

## Connect

`psql -U <user> -d <database>` — Connect to a database with a specific user.

```bash
psql -U postgres -d myapp
```

`psql -h <host> -p <port> -U <user> -d <database>` — Connect to a remote PostgreSQL server.

```bash
psql -h db.example.com -p 5432 -U admin -d myapp
```

`psql '<connection-string>'` — Connect using a connection URI.

```bash
psql 'postgresql://user:pass@host:5432/dbname'
```

`psql -U <user> -d <database> -c '<query>'` — Execute a query and exit.

```bash
psql -U postgres -d myapp -c 'SELECT count(*) FROM users;'
```

`psql -U <user> -d <database> -f <file>` — Execute SQL from a file.

```bash
psql -U postgres -d myapp -f schema.sql
```

## Meta-Commands — Info

`\l` — List all databases.

```bash
\l
```

`\c <database>` — Connect to / switch to a different database.

```bash
\c myapp
```

`\dt` — List all tables in the current schema.

```bash
\dt
```

`\dt+` — List tables with sizes and descriptions.

```bash
\dt+
```

`\d <table>` — Describe a table (columns, types, indexes).

```bash
\d users
```

`\di` — List all indexes.

```bash
\di
```

`\dv` — List all views.

```bash
\dv
```

`\dn` — List all schemas.

```bash
\dn
```

`\df` — List all functions.

```bash
\df
```

## Meta-Commands — Utility

`\x` — Toggle expanded display (vertical output).

```bash
\x auto
```

`\timing` — Toggle query execution timing display.

```bash
\timing
```

`\i <file>` — Execute SQL from a file.

```bash
\i /path/to/migration.sql
```

`\o <file>` — Send query output to a file.

```bash
\o output.txt
```

`\e` — Open the query buffer in an editor.

```bash
\e
```

`\q` — Quit psql.

```bash
\q
```

`\?` — Show help for meta-commands.

```bash
\?
```

## Roles & Permissions

`\du` — List all roles/users.

```bash
\du
```

`CREATE ROLE <name> WITH LOGIN PASSWORD '<pass>';` — Create a new role with login capability.

```bash
CREATE ROLE appuser WITH LOGIN PASSWORD 'secretpass';
```

`GRANT ALL PRIVILEGES ON DATABASE <db> TO <role>;` — Grant all privileges on a database.

```bash
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
```

`GRANT SELECT ON ALL TABLES IN SCHEMA public TO <role>;` — Grant read access to all tables in a schema.

```bash
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
```

`ALTER ROLE <name> WITH <options>;` — Modify role attributes.

```bash
ALTER ROLE appuser WITH CREATEDB;
```

## Import & Export

`pg_dump -U <user> <database> > <file>` — Export a database to SQL.

```bash
pg_dump -U postgres myapp > backup.sql
```

`pg_dump -U <user> -Fc <database> > <file>` — Export in custom compressed format.

```bash
pg_dump -U postgres -Fc myapp > backup.dump
```

`pg_restore -U <user> -d <database> <file>` — Restore from custom format dump.

```bash
pg_restore -U postgres -d myapp backup.dump
```

`\COPY <table> TO '<file>' CSV HEADER` — Export a table to CSV.

```bash
\COPY users TO '/tmp/users.csv' CSV HEADER
```

`\COPY <table> FROM '<file>' CSV HEADER` — Import CSV data into a table.

```bash
\COPY users FROM '/tmp/users.csv' CSV HEADER
```

`pg_dumpall -U <user> > <file>` — Export all databases including roles.

```bash
pg_dumpall -U postgres > all-databases.sql
```

## Common Patterns

`psql -U <user> -d <database> -t -A -c '<query>'` — Query with no headers, no alignment (for scripting).

```bash
psql -U postgres -d myapp -t -A -c 'SELECT email FROM users LIMIT 5'
```

`SELECT pg_size_pretty(pg_database_size('<database>'));` — Show database size in human-readable format.

```bash
SELECT pg_size_pretty(pg_database_size('myapp'));
```

`SELECT * FROM pg_stat_activity;` — Show current connections and running queries.

```bash
SELECT pid, state, query FROM pg_stat_activity;
```

`SELECT version();` — Show PostgreSQL version.

```bash
SELECT version();
```

<!-- PROSE:outro -->
## Conclusion

psql is far more than a prompt for SQL: the backslash meta-commands let you inspect schemas, indexes and roles in seconds, while `pg_dump`/`pg_restore` put complete backups at your fingertips. A word on safety: `DROP`, `TRUNCATE` and a `DELETE` without a `WHERE` clause are irreversible – on production databases, work inside a transaction (`BEGIN; … COMMIT;`) so you can `ROLLBACK` if something looks wrong. Never put passwords in plain text into scripts or the command line: store them in `~/.pgpass` (with `chmod 600`) or pass them via the `PGPASSWORD` environment variable – the same goes for `pg_dump` credentials.

## Further Reading

- [PostgreSQL – psql documentation](https://www.postgresql.org/docs/current/app-psql.html) – complete reference of all options and meta-commands
- [PostgreSQL – manual](https://www.postgresql.org/docs/current/) – official documentation for SQL commands, roles and backups
<!-- PROSE:outro:end -->

## Related Commands

- [artisan](https://www.jpkc.com/db/en/cheatsheets/build-languages/artisan/) – command-line tool for the Laravel framework: migrations, tinker and generators
- [cargo](https://www.jpkc.com/db/en/cheatsheets/build-languages/cargo/) – build tool and package manager for Rust
- [composer](https://www.jpkc.com/db/en/cheatsheets/build-languages/composer/) – dependency manager for PHP