PostgreSQL Cheatsheet

---

---

The PostgreSQL cheatsheet provides quick reference to all the fundamental topics. PostgreSQL is an advanced database system and supports complex data types, while SQL easily manipulates the data. By learning this cheat sheet, one can easily understand the usage of PostgreSQL syntax in real-world applications. Go through this cheat sheet and learn the PostgreSQL database.

Table of Contents

1. Introduction to PostgreSQL

PostgreSQL is an open-source, powerful, and object-relational database system.

SELECT version();

2. Installation of PostgreSQL

To install PostgreSQL on your system, follow the steps below:

• Step 1: Visit the official website https://www.postgresql.org/download/
• Step 2: Choose the appropriate operating system for your system.
• Step 3: Verify the installation.

Below is the command to verify the installation of PostgreSQL −

psql -U postgres

3. Creating TABLE

To create the table in postgreSQL, use the below query −

CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(50)
);

4. Inserting INSERT INTO

To insert the table in postgreSQL, use the INSERT INTO statement.

INSERT INTO table_name (name, age, department) VALUES ('John Doe', 30, 'HR');

5. Fetching Data

To fetch the data from the database, use the SELECT statement.

SELECT * FROM table_name;

6. ADD COLUMN in PostgreSQL

While adding column to the existing table, use the ALTER TABLE statement.

ALTER TABLE table_name ADD COLUMN column_name column_type;

7. Updating TABLE

To modify the existing record from the table, use the UPDATE statement.

UPDATE table_name SET = 50000 WHERE name = 'Tapas';

8. Altering TABLE

To change the column name from the table, use the below query −

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

9. DROP the COLUMN

The DROP statement is used to remove the column from the table.

ALTER TABLE table_name DROP COLUMN column_name;

10. PostgreSQL DELETE

The DELETE statement in PostgreSQL is used to delete the existing record from the table.

DELETE FROM table_name WHERE condition;

11. DROP the TABLE

The DROP TABLE is used to delete the existing records of the table.

DROP TABLE table_name;

12. PostgreSQL Operators

Operators can be used in various SQL statements to perform operations on data.

13. SELECT Statement

The SELECT statement is used to access the data from the database.

SELECT name, age FROM table_name;

14. SELECT Distinct

The SELECT DISTINCT statement is used to return unique/different values.

SELECT DISTINCT department FROM table_name;

15. WHERE

In PostgreSQL, WHERE clause is used to filter the records.

SELECT * FROM table_name WHERE age > 30;

16. ORDER BY

In PostgreSQL, ORDER BY clause is used to sort the result based on ascending or decending order.

SELECT * FROM table_name ORDER BY age DESC;

17. LIMIT

The LIMIT clause is used to return the maximum number of records.

SELECT * FROM table_name LIMIT 5;

18. MIN and Max in PostgreSQL

Both these functions are commonly known as aggregate function. The MIN() returns the smallest value in a specified column whereas MAX() returns the highest value from the table.

i. PostgreSQL − MIN()

SELECT 
    MIN(column_name) 
FROM 
    table_name;

ii. PostgreSQL − MAX()

SELECT 
    MIN(column_name) 
FROM 
    table_name;

19. COUNT

The COUNT function of PostgreSQL return the number of rows based on specified condition.

SELECT COUNT(*) FROM table_name;

20. SUM

The SUM() calculate the numeric value of specified column.

SELECT SUM(COLUMN_NAME) FROM table_name;

21. AVG

The AVG() calculate the average value of specified column.

SELECT AVG(salary) FROM table_name;

22. LIKE

You can use LIKE operator with WHERE clause to search for specified pattern from a column.

SELECT * FROM table_name WHERE name LIKE 'A%'; -- Names starting with 'A'
SELECT * FROM table_name WHERE name LIKE '_a%'; -- Names with 'a' as the second letter

23. IN

The IN operator allows user to specify the list of possible values in the WHERE clause.

SELECT * FROM table_name WHERE column_name IN ('VALUE1', 'VALUE2', 'VALUE3');

24. BETWEEN

The BETWEEN operator is used to filter the record within the range. This works with numbers, dates, and text.

SELECT * FROM table_name WHERE column_name BETWEEN VALUE1 AND VALUE2;

25. AS

An "AS" is known for alias which used to rename columns or tables.

SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

27. INNER JOIN

The INNER JOIN returns only matching rows from both tables.

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

28. RIGHT JOIN

The RIGHT JOIN returns all rows from the right table + matching rows from the left table.

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

29. FULL JOIN

The FULL JOIN returns all rows from both tables (matches + non-matching rows).

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

30. CROSS JOIN

The CROSS JOIN returns the Cartesian product (every row in table A joins with every row in table B).

SELECT column1, column2
FROM table1
CROSS JOIN table2;

31. UNION

The UNION is a part of set operation that combines results from two queries, removing duplicates.

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

32. GROUP BY

In PostgreSQL, GROUP BY clause groups rows with the same values in specified columns.

SELECT column_name, COUNT(*) FROM table_name
GROUP BY column_name;

33. HAVING

In PostgreSQL, HAVING clause filters grouped data (like WHERE but after aggregation).

SELECT column_name, AVG(column_value) FROM table_name
GROUP BY column_name
HAVING AVG(column_value) > VALUE;

34. EXISTS

The EXISTS returns TRUE if a subquery returns any rows.

SELECT column_name FROM table_name t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.common_column = t1.common_column);

35. ANY

In PostgreSQL, ANY compares a value to any value in a subquery result (like OR).

SELECT * FROM table_name WHERE column_value > ANY (SELECT column_value FROM table_name WHERE column_name = 'VALUE');

36. ALL

In PostgreSQL, ALL compares a value to all values in a subquery result (like AND).

SELECT * FROM table_name WHERE column_value > ALL (SELECT column_value FROM table_name WHERE column_name = 'VALUE');

37. CASE

In PostgreSQL, CASE works like IF-ELSE and returns different values based on conditions.

SELECT column_name,
  CASE
    WHEN column_value > VALUE THEN 'High'
    WHEN column_value BETWEEN VALUE1 AND VALUE2 THEN 'Medium'
    ELSE 'Low'
  END AS column_alias
FROM table_name;