This guide serves as a comprehensive reference for psql, the interactive terminal for PostgreSQL, based on the official documentation. psql allows users to interactively enter, edit, and execute SQL commands while providing internal meta-commands to simplify database management.
1. Getting Started and Connection
To start psql, you typically invoke it from your command shell followed by the database name.
- Connect to a database:
psql mydb(defaults to the current user’s role). - Connect as a specific user:
psql -U username mydb. - Prompt Indicators:
dbname=>: Indicates a normal user session.dbname=#: Indicates you are a database superuser, who is not subject to standard access controls.
2. Help and Navigation Meta-Commands
Internal commands in psql always begin with a backslash (\).
\?: Displays help for all internalpsqlmeta-commands.\h [SQL_COMMAND]: Provides syntax help for SQL commands (e.g.,\h CREATE TABLE).\q: Exits thepsqlterminal and returns to the command shell.\i filename: Reads and executes SQL commands from a specified file.\gset: Executes a query and stores the results intopsqlvariables for future use.
3. Listing and Informational Commands
These commands allow you to explore the structure of your database cluster. Adding a + (e.g., \dt+) provides additional technical details like comments and physical storage size.
\l: Lists all databases within the cluster, including their encoding and collations.\dn: Lists all schemas in the current database.\dt: Lists all tables in the current schema [psql help, 1760].\dv: Lists all views [psql help].\df: Lists all available functions and procedures.\do: Lists available operators.\db: Lists all tablespaces and their physical locations.\dOS: Lists available locales and collations supported by the operating system or ICU provider.
4. Object Inspection and Security
\d [name]: Describes a specific object (table, view, index, or sequence). For a table, it lists columns, data types, constraints, and replication publications.\du: Lists all database roles (users and groups) and their specific attributes, such asSuperuserorCreate role.\dp [pattern]: Displays access privileges (ACLs). This shows which roles have permissions likeSELECT(r),INSERT(a), orUPDATE(w) on a specific table.\dAc,\dAf,\dAo: Inspects operator classes, families, and operators respectively.
5. Advanced Monitoring and Specialized Commands
Text Search Inspection
\dF: Lists full text search configurations (e.g.,english,russian).\dFd: Lists text search dictionaries.\dFp: Lists text search parsers.\dFt: Lists text search templates.
Logical Replication
\dRp: Displays available logical replication publications, including which tables they cover and their associated row filters.
6. Session and Display Management
\encoding [value]: Shows or changes the client character set encoding (e.g.,\encoding SJIS).\password [role]: Safely changes the password for a specific role without showing it in cleartext in logs.\x [on|off|auto]: Toggles expanded display mode. This is extremely useful when viewing rows with many columns that would otherwise wrap on the screen.\set VERBOSITY verbose: Increases the detail of error messages to include SQLSTATE codes and source code locations.
Summary Table: ACL Privilege Abbreviations
When using \dp, permissions are shown as single letters:
| Code | Privilege | Applies To |
|---|---|---|
| r | SELECT (“read”) | Tables, columns, sequences |
| a | INSERT (“append”) | Tables, columns |
| w | UPDATE (“write”) | Tables, columns, sequences |
| d | DELETE | Tables |
| D | TRUNCATE | Tables |
| C | CREATE | Databases, schemas, tablespaces |
| c | CONNECT | Databases |
| X | EXECUTE | Functions, procedures |
| U | USAGE | Schemas, languages, types, sequences |
Examples
- Inspecting a table’s structure and replication status:
\d my_table - Checking which users can access a table:
\dp my_table - Changing your own password:
\password - Viewing detailed information about the ‘english’ search configuration:
\dF+ english