logo
eng-flag

PostgreSQL Cheatsheet

Table of Contents

  1. Basic Commands
  2. Database Operations
  3. Table Operations
  4. Data Manipulation
  5. Querying Data
  6. Joins
  7. Aggregations
  8. Indexing
  9. Transactions
  10. Views
  11. Functions and Procedures
  12. Triggers
  13. User Management
  14. Backup and Restore
  15. Performance Tuning

Basic Commands

Connect to PostgreSQL

psql -U username -d database_name

List all databases

l

List all tables in the current database

dt

Describe a table

d table_name

Exit psql

q

Database Operations

Create a new database

CREATE DATABASE database_name;

Drop a database

DROP DATABASE database_name;

Rename a database

ALTER DATABASE old_name RENAME TO new_name;

Table Operations

Create a new table

CREATE TABLE table_name (
    column1 datatype1 constraints,
    column2 datatype2 constraints,
    ...
);

Example:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary NUMERIC(10, 2)
);

Alter table

-- Add a column
ALTER TABLE table_name ADD COLUMN column_name datatype;

-- Drop a column
ALTER TABLE table_name DROP COLUMN column_name;

-- Rename a column
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- Change column data type
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;

Drop a table

DROP TABLE table_name;

Data Manipulation

Insert data

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-15', 50000.00);

Update data

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET salary = 55000.00
WHERE id = 1;

Delete data

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE id = 1;

Querying Data

Basic SELECT

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Example:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, last_name ASC;

LIMIT and OFFSET

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET start_row;

Example:

SELECT *
FROM employees
LIMIT 10 OFFSET 20;

DISTINCT

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example:

SELECT DISTINCT department
FROM employees;

Joins

INNER JOIN

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT employees.first_name, employees.last_name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

LEFT JOIN

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT JOIN

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

FULL OUTER JOIN

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Aggregations

COUNT

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example:

SELECT COUNT(*) as total_employees
FROM employees;

SUM

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example:

SELECT SUM(salary) as total_salary
FROM employees;

AVG

SELECT AVG(column_name)
FROM table_name
WHERE condition;

MIN and MAX

SELECT MIN(column_name), MAX(column_name)
FROM table_name
WHERE condition;

GROUP BY

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Example:

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

HAVING

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Example:

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Indexing

Create an index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example:

CREATE INDEX idx_last_name
ON employees (last_name);

Create a unique index

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Drop an index

DROP INDEX index_name;

Transactions

Begin a transaction

BEGIN;

Commit a transaction

COMMIT;

Rollback a transaction

ROLLBACK;

Example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Views

Create a view

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW high_salary_employees AS
SELECT *
FROM employees
WHERE salary > 75000;

Drop a view

DROP VIEW view_name;

Functions and Procedures

Create a function

CREATE FUNCTION function_name(parameter1 datatype, parameter2 datatype, ...)
RETURNS return_datatype AS $$
BEGIN
    -- function body
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Example:

CREATE FUNCTION get_employee_salary(emp_id integer)
RETURNS numeric AS $$
DECLARE
    emp_salary numeric;
BEGIN
    SELECT salary INTO emp_salary
    FROM employees
    WHERE id = emp_id;
    
    RETURN emp_salary;
END;
$$ LANGUAGE plpgsql;

Call a function

SELECT function_name(argument1, argument2, ...);

Example:

SELECT get_employee_salary(1);

Triggers

Create a trigger

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function();

Example:

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_customer_modtime
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

User Management

Create a user

CREATE USER username WITH PASSWORD 'password';

Grant privileges

GRANT privilege_type ON table_name TO username;

Example:

GRANT SELECT, INSERT, UPDATE ON employees TO john_doe;

Revoke privileges

REVOKE privilege_type ON table_name FROM username;

Backup and Restore

Backup a database

pg_dump database_name > backup_file.sql

Restore a database

psql database_name < backup_file.sql

Performance Tuning

EXPLAIN

Use EXPLAIN to show the query plan:

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

ANALYZE

Update statistics used by the query planner:

ANALYZE table_name;

Vacuum

Clean up dead tuples and optionally analyze the database:

VACUUM (ANALYZE) table_name;

Create materialized view

CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Refresh materialized view

REFRESH MATERIALIZED VIEW view_name;

2024 © All rights reserved - buraxta.com