PostgreSQL Cheat Sheet

PostgreSQL is a powerful, open-source relational database system known for its robustness and flexibility. It supports complex queries, custom data types, and a wide range of extensions, making it a top choice for both small projects and large enterprises.

In this post, we'll cover essential psql commands that will help you manage and interact with your PostgreSQL database effectively.

Access the PostgreSQL server from psql

psql -U postgres

Useful Flags in psql

Here are some interesting flags you can use with psql (you can see all options by using -h or --help depending on your psql version):

• -E: Describes the underlying queries of the \ commands (great for learning how they work).
• -l: Lists all databases and then exits. This is particularly useful if the user you are connecting with doesn't have a default database, like in AWS RDS.

Common psql Meta-Commands

These meta-commands (\ commands) are crucial for database exploration and management. Most of them support additional parameters like schema.name and accept wildcards such as *.*.

• \?: Show help (lists all available commands with explanations).
• \q: Quit/Exit the psql session.
• \c database: Connect to a specific database.
• \d table: Show the definition of a table (columns, etc.), including triggers.
• \d+ table: Provides a more detailed table definition, including descriptions and physical disk size.
• \l: List all databases.
• \dy: List events.
• \df: List functions.
• \di: List indexes.
• \dn: List schemas.
• \dt *.*: List tables from all schemas (if *.* is omitted, it will only show tables from the SEARCH_PATH schemas).
• \dT+: List all data types.
• \dv: List views.
• \dx: List all installed extensions.
• \df+ function: Show the SQL code of a function.
• \x: Pretty-format query results, instead of the default ASCII tables.
• \copy (SELECT * FROM table_name) TO 'file_path_and_name.csv' WITH CSV: Export a table as a CSV file.
• \des+: List all foreign servers.
• \dE[S+]: List all foreign tables.
• \! bash_command: Execute a Bash command from within psql (e.g., \! ls).

User Management Commands

Here are some commands related to managing users and roles:

• \du: List all users.
• \du username: Show details for a specific username.
• create role test1: Create a role with the specified username.
• create role test2 noinherit login password 'password';: Create a role with a username and password.
• set role test;: Change the role for the current session to test.
• grant test2 to test1;: Allow test1 to assume the role of test2.
• \deu+: List all user mappings on the server.

Service Management Commands

Here are some basic commands to manage the PostgreSQL service:

• systemctl start postgresql-14: Start the PostgreSQL 14 service.
• systemctl enable postgresql-14: Enable PostgreSQL 14 to start on boot.
• systemctl stop postgresql-14: Stop the PostgreSQL 14 service.