postgresql cheat sheet

3 min read 19-10-2024

PostgreSQL Cheat Sheet: A Guide to Essential Commands and Concepts

PostgreSQL, a powerful and robust open-source relational database system, is a popular choice for developers and data scientists. This cheat sheet aims to provide a quick reference to essential PostgreSQL commands, concepts, and best practices.

1. Connecting to PostgreSQL

To start interacting with a PostgreSQL database, you'll need to establish a connection. Here's how you can do it using psql:

Using psql

psql -h hostname -p port -U username -d database_name

Explanation:

-h hostname: Specifies the hostname or IP address of the PostgreSQL server.
-p port: Specifies the port number on which the PostgreSQL server is listening (default is 5432).
-U username: Specifies the username to connect with.
-d database_name: Specifies the database name to connect to.

Example:

psql -h localhost -U postgres -d mydatabase

This command connects to the mydatabase database on the local machine using the postgres user.

2. Basic SQL Commands

Creating a Table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL
);

Inserting Data:

INSERT INTO users (username, email, password) VALUES ('johndoe', '[email protected]', 'password123');

Selecting Data:

SELECT * FROM users WHERE id = 1;

Updating Data:

UPDATE users SET email = '[email protected]' WHERE id = 1;

Deleting Data:

DELETE FROM users WHERE id = 1;

3. Data Types and Operators

PostgreSQL offers a wide range of data types to represent different kinds of information. Here are some common ones:

Numeric: INTEGER, BIGINT, DECIMAL, NUMERIC
Text: VARCHAR, TEXT, CHAR
Date and Time: DATE, TIME, TIMESTAMP
Boolean: BOOLEAN

You can use various operators to manipulate and compare data:

Arithmetic Operators: +, -, *, /, %
Comparison Operators: =, !=, >, <, >=, <=
Logical Operators: AND, OR, NOT

Example:

SELECT * FROM users WHERE username LIKE 'j%'; -- Selects users whose username starts with "j"

4. Functions and Aggregations

PostgreSQL provides numerous built-in functions to perform various operations.

Common Functions:

NOW(), CURRENT_DATE: Get the current date and time.
UPPER(), LOWER(), TRIM(), SUBSTRING(), LENGTH(), REPLACE(), CONCAT(), COALESCE(), CASE WHEN: String manipulation and conditional operations.
AVG(), SUM(), COUNT(), MIN(), MAX(), GROUP BY, HAVING: Aggregate functions and grouping data.

Example:

SELECT COUNT(*) AS total_users FROM users; -- Counts all users

5. Constraints and Indexes

Constraints ensure data integrity and enforce specific rules on your data. Indexes speed up data retrieval by creating sorted structures.

Common Constraints:

PRIMARY KEY: Ensures uniqueness and identifies each row in a table.
UNIQUE: Enforces uniqueness on a column or set of columns.
NOT NULL: Prevents a column from being empty.
FOREIGN KEY: Creates a relationship between tables.
CHECK: Defines a condition that must be true for data to be valid.

Creating an Index:

CREATE INDEX users_username_idx ON users (username); -- Creates an index on the 'username' column

6. Transactions and Concurrency

Transactions ensure data consistency and atomicity. Here are some key concepts:

BEGIN TRANSACTION: Starts a new transaction.
COMMIT: Saves changes made during the transaction.
ROLLBACK: Reverts all changes made during the transaction.
SAVEPOINT: Marks a point within a transaction to allow partial rollbacks.

7. Advanced Features

PostgreSQL offers advanced features like stored procedures, triggers, views, and more.

Stored Procedures: Reusable blocks of code that can perform complex operations.
Triggers: Automatically execute specific code when certain events occur in the database.
Views: Virtual tables based on underlying data, providing different perspectives on the data.

Example:

CREATE OR REPLACE FUNCTION get_user_by_id(id INT) RETURNS users AS $
BEGIN
  SELECT * FROM users WHERE id = get_user_by_id.id;
END;
$ LANGUAGE plpgsql;

8. Useful Resources

PostgreSQL Documentation: https://www.postgresql.org/docs/
PostgreSQL Tutorial: https://www.postgresqltutorial.com/
Stack Overflow: https://stackoverflow.com/questions/tagged/postgresql

This cheat sheet provides a starting point for working with PostgreSQL. Remember to explore the extensive documentation and resources available to delve deeper into its capabilities and expand your knowledge.

postgresql cheat sheet