PostgreSQL cheatsheet guide

postgresql.md

PostgreSQL Cheatsheet

Basic Commands

• \l: List all databases.
• \c database_name: Connect to a specific database.
• \dt: List all tables in the current database.
• \d table_name: Show information about a specific table.
• \du: List all database users.
• \q: Quit psql.

Creating Databases and Tables

• CREATE DATABASE database_name;: Create a new database.
• DROP DATABASE database_name;: Delete a database.
• CREATE TABLE table_name (column1 datatype, column2 datatype, ...);: Create a new table.
• DROP TABLE table_name;: Delete a table.

Altering Tables

• ALTER TABLE table_name ADD COLUMN column datatype;: Add a new column to a table.
• ALTER TABLE table_name DROP COLUMN column;: Remove a column from a table.
• ALTER TABLE table_name ALTER COLUMN column TYPE datatype;: Change the data type of a column.
• ALTER TABLE table_name RENAME COLUMN old_name TO new_name;: Rename a column.
• ALTER TABLE table_name RENAME TO new_table_name;: Rename a table.

Data Types

• INTEGER: Whole numbers.
• NUMERIC(precision, scale): Fixed-point or floating-point numbers.
• VARCHAR(length): Variable-length character strings.
• DATE: Dates (YYYY-MM-DD).
• TIMESTAMP: Dates and times.
• BOOLEAN: True or false values.
• JSON: JSON data.

Data Manipulation

• INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);: Insert a new row into a table.
• UPDATE table_name SET column = value WHERE condition;: Update existing records in a table.
• DELETE FROM table_name WHERE condition;: Delete records from a table.

Querying Data

• SELECT column1, column2, ... FROM table_name;: Retrieve data from a table.
• SELECT column1, column2, ... FROM table_name WHERE condition;: Retrieve data based on a condition.
• SELECT column1, column2, ... FROM table_name ORDER BY column ASC/DESC;: Retrieve data sorted in ascending or descending order.
• SELECT column1, column2, ... FROM table_name LIMIT count OFFSET offset;: Retrieve a specific number of rows starting from an offset.

Filtering

• WHERE: Filters data based on a condition.
• AND: Combines multiple conditions, all of which must be true.
• OR: Combines multiple conditions, at least one of which must be true.
• BETWEEN: Matches a range of values.
• IN: Matches any value in a list.
• LIKE: Matches patterns using wildcards (% and _).
• IS NULL: Matches null values.
• IS NOT NULL: Matches non-null values.

Aggregation Functions

• COUNT(column): Returns the number of rows.
• SUM(column): Returns the sum of values in a column.
• AVG(column): Returns the average value of a column.
• MIN(column): Returns the minimum value in a column.
• MAX(column): Returns the maximum value in a column.
• GROUP BY column: Groups rows based on a column.
• HAVING condition: Filters data after grouping.

Joining Tables

• INNER JOIN: Returns rows that have matching values in both tables.
• LEFT JOIN: Returns all rows from the left table and the matching rows from the right table.
• RIGHT JOIN: Returns all rows from the right table and the matching rows from the left table.
• FULL JOIN: Returns all rows when there is a match in either the left or right table.

Indexes

• CREATE INDEX index_name ON table_name (column);: Create an index on a table column.
• DROP INDEX index_name;: Delete an index.

Permissions and Privileges

In the GRANT command, the privilege(s) section refers to the specific privileges or permissions that you can grant to a user on a table. Here are some commonly used privileges in PostgreSQL:

SELECT: Allows the user to read/query data from the table.
INSERT: Allows the user to insert new rows into the table.
UPDATE: Allows the user to modify/update existing rows in the table.
DELETE: Allows the user to delete rows from the table.
REFERENCES: Allows the user to create foreign key constraints referencing the table.
TRIGGER: Allows the user to create triggers on the table.
ALL: Grants all available privileges on the table.

• GRANT privilege(s) ON table_name TO user;: Grant specific privileges on a table to a user.
• REVOKE privilege(s) ON table_name FROM user;: Revoke specific privileges on a table from a user.
• GRANT privilege(s) ON ALL TABLES IN SCHEMA schema_name TO user;: Grant specific privileges on all tables in a schema to a user.
• REVOKE privilege(s) ON ALL TABLES IN SCHEMA schema_name FROM user;: Revoke specific privileges on all tables in a schema from a user.
• GRANT privilege(s) ON DATABASE database_name TO user;: Grant specific privileges on a database to a user.
• REVOKE privilege(s) ON DATABASE database_name FROM user;: Revoke specific privileges on a database from a user.
• GRANT role TO user;: Grant a role to a user.
• REVOKE role FROM user;: Revoke a role from a user.
• GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;: Give a user all permissions on a database.
• ALTER DATABASE database_name OWNER TO user_name;: Set the owner of a database to a user.
• GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_name;: Give a user all permissions on all tables in the public schema.

Creating Users

• CREATE USER username WITH PASSWORD 'password';: Create a new user with a password.
• CREATE USER username;: Create a new user without a password (password will need to be set later).
• CREATE USER username CREATEDB;: Create a new user with the ability to create databases.
• CREATE USER username WITH ENCRYPTED PASSWORD 'password';: Create a new user with an encrypted password.

Modifying Users

• ALTER USER username WITH PASSWORD 'new_password';: Change the password for a user.
• ALTER USER username WITH SUPERUSER;: Grant superuser privileges to a user.
• ALTER USER username WITH NOSUPERUSER;: Revoke superuser privileges from a user.
• ALTER USER username WITH CREATEDB;: Grant the ability to create databases to a user.
• ALTER USER username WITH NOCREATEDB;: Revoke the ability to create databases from a user.
• ALTER USER username WITH LOGIN;: Allow a user to log in.
• ALTER USER username WITH NOLOGIN;: Prevent a user from logging in.
• ALTER USER username RENAME TO new_username;: Rename a user.

Deleting Users

• DROP USER username;: Delete a user.
• DROP USER IF EXISTS username;: Delete a user if it exists.

Granting and Revoking Permissions

• GRANT permission_name ON table_name TO username;: Grant a specific permission on a table to a user.
• REVOKE permission_name ON table_name FROM username;: Revoke a specific permission on a table from a user.
• GRANT ALL PRIVILEGES ON table_name TO username;: Grant all privileges on a table to a user.
• REVOKE ALL PRIVILEGES ON table_name FROM username;: Revoke all privileges on a table from a user.
• GRANT role_name TO username;: Grant a role to a user.
• REVOKE role_name FROM username;: Revoke a role from a user.

Listing Users and Permissions

• \du: List all database users.
• \l: List all databases and their owners.
• \dp table_name: Show the permissions of a specific table.

This cheatsheet provides an overview of the most commonly used PostgreSQL commands and syntax. For more detailed information, refer to the PostgreSQL documentation.