PostgreSQL is a powerful open-source relational database management system (RDBMS) known for its extensibility and advanced features. This cheat sheet presents key commands and queries to interact effectively with PostgreSQL databases.
Connecting to PostgreSQL:
Command
Description
psql -U [username] -d [database]
Connect to PostgreSQL server with username and database.
\l
List all databases.
\c [database_name]
Connect to a specific database.
Managing Tables:
Command
Description
CREATE TABLE [table_name] ([column_definitions]);
Create a new table with specified columns.
\dt
List all tables in the current database.
\d [table_name]
Display the structure of a table.
Inserting Data:
Command
Description
INSERT INTO [table_name] VALUES (value1, value2, ...);
Insert data into a table.
INSERT INTO [table_name] (column1, column2, ...) VALUES (value1, value2, ...);
Insert data with specified columns.
Querying Data:
Command
Description
SELECT * FROM [table_name];
Retrieve all columns from a table.
SELECT column1, column2 FROM [table_name] WHERE condition;
Retrieve specific columns with conditions.
UPDATE [table_name] SET column1 = value1 WHERE condition;
Update data in a table.
DELETE FROM [table_name] WHERE condition;
Delete data from a table.
Filtering and Sorting:
Command
Description
SELECT * FROM [table_name] WHERE condition ORDER BY column_name ASC/DESC;
Filter and sort query results.
LIMIT [number];
Limit the number of rows returned.
Aggregation Functions:
Command
Description
SELECT COUNT(column_name) FROM [table_name];
Count the number of rows in a table.
SELECT AVG(column_name) FROM [table_name];
Calculate the average value of a column.
SELECT MAX(column_name) FROM [table_name];
Retrieve the maximum value in a column.
SELECT MIN(column_name) FROM [table_name];
Retrieve the minimum value in a column.
SELECT SUM(column_name) FROM [table_name];
Calculate the sum of values in a column.
Conclusion
This PostgreSQL cheat sheet serves as a handy reference for both beginners and experienced users, offering essential commands for managing databases, tables, and data. PostgreSQL’s versatility and rich feature set make it a popular choice for various applications. Customize these commands according to your specific PostgreSQL setup and database requirements.