psql cheat sheet

2 min read 21-10-2024

psql Cheat Sheet: Mastering the PostgreSQL Command Line

The PostgreSQL command line interface, psql, is a powerful tool for interacting directly with your database. Whether you're a seasoned database administrator or just starting out, having a handy cheat sheet can make your life easier. This article will guide you through essential psql commands, complete with explanations and practical examples.

Note: This article is based on information from the official PostgreSQL documentation and contributions from the GitHub community.

Connecting to Your Database

psql -h hostname -p port -U username -d database
- This command connects to a PostgreSQL database on a specific host, port, using a particular username and database name.
- Example: psql -h localhost -p 5432 -U postgres -d my_database
psql (without any arguments)
- Connects to the default database using the default settings defined in your PostgreSQL configuration.

Essential Commands

\c database
- Connects to a different database within the same server.
- Example: \c my_new_database
\l or \list
- Lists all databases available to the current user.
\dt
- Lists all tables within the current database.
\d table_name
- Displays the structure of a specific table, including columns, data types, and constraints.
\d+ table_name
- Provides a detailed description of the table structure, including indexes, triggers, and other objects.
\ds
- Lists all sequences in the current database.
\df
- Lists all functions in the current database.
\h command
- Provides help for a specific command, including syntax and examples.
- Example: \h \d
\q
- Exits psql.

Querying and Modifying Data

SELECT * FROM table_name;
- Retrieves all data from a table.
SELECT column1, column2 FROM table_name WHERE condition;
- Retrieves specific columns based on a given condition.
- Example: SELECT name, age FROM users WHERE age > 21;
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- Inserts new data into a table.
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
- Updates existing data in a table based on a condition.
DELETE FROM table_name WHERE condition;
- Deletes rows from a table based on a condition.

Tips and Tricks

\g
- Executes a command and sends the output to the standard output, allowing you to use the output in a pipeline.
\copy table_name (column1, column2) FROM 'file.csv' WITH (FORMAT CSV, HEADER);
- Imports data from a CSV file into a table.
\copy table_name (column1, column2) TO STDOUT WITH (FORMAT CSV, HEADER);
- Exports data from a table into a CSV file.
\echo 'Your message here'
- Prints a message to the terminal.

Advanced Features

psql -f script.sql
- Executes a SQL script stored in a file.
- Example: psql -f create_tables.sql
\set
- Sets variables within the psql session.
- Example: \set my_variable 'test'
\! command
- Executes a system shell command.
- Example: \! ls -l

Conclusion

This psql cheat sheet provides a starting point for navigating the PostgreSQL command line interface. Remember to explore the official documentation and experiment with the different commands to master your database interactions.