Download PostgreSQL Cheat Sheet PDF for Quick References

PostgreSQL is a free and open-source relational database management system (RDBMS) known as Postgres.  

This RDBMS provides various features, including automatically updating views, triggers, materialized views, stored procedures, foreign keys, and transactions with ACID (Atomicity, Consistency, Isolation, and Durability) properties.

PostgreSQL runs on all major operating systems, including Windows, macOS, Linux, and OpenBSD. However, it’s the default database system for the macOS server. This database system can handle a wide range of workloads. Moreover, it comes with powerful add-ons, like the popular PostGIS geospatial database extender.

Since PostgreSQL has applications in data science, many professionals are eager to add it to their skill set. What if there was a Postgres command line cheat sheet to assist you with daily tasks and development?

You’ve come to the right place. We’ve created this PostgreSQL cheat sheet with syntax, commands, queries, and examples to serve as a quick reference while working with PostgreSQL databases.

Click here to download Hackr.io’s PostgreSQL Cheat Sheet PDF.

Let’s dive in!

PostgreSQL Cheat Sheet

Section 1 - PostgreSQL Queries Cheat Sheet: Querying Data

Let’s discuss different commands or queries for querying data in the PostgreSQL database.

1. Select 

This commonly used query allows you to retrieve information from the tables. It’s also one of the most complex statements, as it comes with several clauses. Thus, we have divided it into several sections for better understanding.

Syntax:

SELECT
  select_list
FROM
  table_name;

Where:

Select_list specifies the number of columns from a specific table that you want to retrieve. If you mention multiple columns, you must separate them by commas. Also, you can use (*) for retrieving data from all columns from a particular table.

The table_name specifies the name of the table from which you want to retrieve the data.

• Querying data from one column

SELECT first_name FROM customer;

Here, the semicolon specifies the PostgreSQL statement’s end.

• Querying data from multiple columns

SELECT
  first_name,
  last_name,
  email
FROM
  customer;

• Querying data from all columns of a table.

SELECT * FROM customer;

We don’t recommend using (*), as it impacts database and application performance. So, explicitly specify the names of all columns.

• Select statement with expression.

SELECT 
  first_name || ' ' || last_name,
  email
FROM 
  customer;

(||) is the concatenation operator.

You can also simply use the SELECT statement without the conjunction of any clauses:

SELECT 5 * 3;

2. Column Alias

You can use the column alias to assign a temporary name for the column or expression. This alias exists temporarily during the query’s execution.

Syntax:

SELECT column_name AS alias_name
FROM table_name;

You can also omit the AS from the above syntax:

SELECT column_name alias_name
FROM table_name;

Or

SELECT expression AS alias_name
FROM table_name;

• Assigning column alias

Let’s say we need to retrieve all the customers’ first and last names from the table. We use the following query:

SELECT 
  first_name,
  last_name
FROM customer;

Now, we’ll change the last_name with an alias:

SELECT 
  first_name,
  last_name AS surname
FROM customer;

The column name ‘last_name’ changes to ‘surname’.

You can also use the below query, providing the same result (omitting the as).

SELECT 
  first_name,
  last_name surname
FROM customer;

• Assigning column alias to an expression.

This will retrieve all the customers’ first and last names with a space in between them.

SELECT 
  first_name || ' ' || last_name
FROM 
  customer;

The above query will display a table without any specific column name.

Now, let us assign an alias for the concatenated column as “full_name:”

SELECT
   first_name || ' ' || last_name AS full_name
FROM
   customer;

You’ll get a table with the column name “full_name.”

• Assign column alias with spaces

column_name AS "column alias"

For example:

SELECT
   first_name || ' ' || last_name "full name"
FROM
   customer;

Learn Postgresql From A Top-rated Database Instructor!

The above query will display a table with column name ‘full_name’.

3. Order By

“Select” query results aren’t organized. So, you can organize it in ascending or descending order using the “order by” clause.

Syntax:

SELECT
        select_list
FROM
        table_name
ORDER BY
        sort_expression1 [ASC | DESC],
       ...
        sort_expressionN [ASC | DESC];

In the above syntax, the sort expression can be a column or an expression that you want to sort after the ORDER BY keywords. For sorting data based on multiple columns or expressions, you must place a comma (,) between two columns or expressions to separate them. Then, you must specify ascending or descending.

Note: The ‘order by’ clause must be be the last in any select query.

• Sorting rows by one column.

SELECT
        first_name,
        last_name
FROM
        customer
ORDER BY
        first_name ASC;

This query will display a table with a single column having the first names of all customers in ascending order.

Also, ascending is the default if you do not specify the (asc or desc) with the order by clause

Therefore, the following query will display the same output:

SELECT
        first_name,
        last_name
FROM
        customer
ORDER BY
        first_name;

• Sorting rows by multiple columns.

SELECT
        first_name,
        last_name
FROM
        customer
ORDER BY
        first_name ASC,
        last_name DESC;

The above query will display a table with two columns, first_name and last_name. The first_name column will have the customers’ first names in the ascending order, while the last_name column has the last names of customers in the descending order.

• Sorting rows by expression.

SELECT 
        first_name,
        LENGTH(first_name) len
FROM
        customer
ORDER BY 
        len DESC;

This query will display a table with two columns, first_name and their lengths. It sorts the rows in descending order based on the first name’s length.

• Sorting rows with null values.

Null represents the missing or unknown data. You can even sort the rows with null values.

ORDER BY sort_expresssion [ASC | DESC] [NULLS FIRST | NULLS LAST]

-- create a new table

CREATE TABLE sort_demo(
        num INT
);

-- insert some data

INSERT INTO sort_demo(num)
VALUES(1),(2),(3),(null);

• Sorting rows with null values.

SELECT num
FROM sort_demo
ORDER BY num;​

As you can see, Null values will be last by default.

The following query will provide the same result.

SELECT num
FROM sort_demo
ORDER BY num NULLS LAST;

But to get the null valued rows in the first place, you must mention it explicitly:

SELECT num
FROM sort_demo
ORDER BY num NULLS FIRST;

You will get the following output:

4. Select Distinct

This removes duplicate rows from a result set. You can apply the DISTINCT clause to one or more columns in the select list of the SELECT statement.

Syntax:

SELECT
  DISTINCT column1
FROM
  table_name;

Or:

SELECT
  DISTINCT column1, column2
FROM
  table_name;

Or:

SELECT
  DISTINCT ON (column1) column_alias,
  column2
FROM
  table_name
ORDER BY
  column1,
  column2;

For example, we will create a table distinct_demo using the following query and insert some data.

CREATE TABLE distinct_demo (
        id serial NOT NULL PRIMARY KEY,
        bcolor VARCHAR,
        fcolor VARCHAR
);

Insert rows using the following query.

INSERT INTO distinct_demo (bcolor, fcolor)
VALUES
        ('red', 'red'),
        ('red', 'red'),
        ('red', NULL),
        (NULL, 'red'),
        ('red', 'green'),
        ('red', 'blue'),
        ('green', 'red'),
        ('green', 'blue'),
        ('green', 'green'),
        ('blue', 'red'),
        ('blue', 'green'),
        ('blue', 'blue');

Without using distinct clause

SELECT
        id,
        bcolor,
        fcolor
FROM
        distinct_demo ;

Output:

• Distinct clause on one column.

SELECT
        DISTINCT bcolor
FROM
        distinct_demo
ORDER BY
        bcolor;

Output:

• Distinct on multiple columns.

SELECT
        DISTINCT bcolor,
        fcolor
FROM
        distinct_demo
ORDER BY
        bcolor,
        fcolor;

Output:

• Distinct on example.

SELECT
        DISTINCT ON (bcolor) bcolor,
        fcolor
FROM
        distinct_demo
ORDER BY
        bcolor,
        fcolor;

Output:

Section 2 - Filtering Data

Now, let’s see the different classes we can use with the SELECT statement to filter and retrieve data from database tables.

1. Where Clause

It is used to retrieve rows based on a specific condition mentioned.

Syntax:

SELECT select_list
FROM table_name
WHERE condition
ORDER BY sort_expression

The condition must evaluate as true, false, or unknown. It can be a boolean expression or a combination of boolean expressions using the AND and OR operators. Only the rows satisfying the condition will be returned.

In the where clause, you can use logical and comparison operators:

• Using (=) operator.

SELECT
        last_name,
        first_name
FROM
        customer
WHERE
        first_name = 'Jamie';

The above query returns a table with two columns, last_name and first_name, where the values on the first_name column only consist of Jamie.

• Using AND operator.

SELECT
        last_name,
        first_name
FROM
        customer
WHERE
        first_name = 'Jamie' AND 
       last_name = 'Rice';

The above returns the table containing the values in the last_name column as Rice and in the first_name column as Jamie.

• Using OR operator.

SELECT
        first_name,
        last_name
FROM
        customer
WHERE
        last_name = 'Rodriguez' OR 
        first_name = 'Adam';

This query returns a table containing values whose last_name is Rodriguez, or the first_name is Adam.

• Using IN operator.

SELECT
        first_name,
        last_name
FROM
        customer
WHERE 
        first_name IN ('Ann','Anne','Annie');

It returns a table containing the values whose first name is Ann, Anne, and Annie.

• Using LIKE operator.

SELECT
        first_name,
        last_name
FROM
        customer
WHERE 
        first_name LIKE 'Ann%'

This query returns a table whose first_name column values start with ‘Ann’.

• Using BETWEEN operator.

SELECT
        first_name,
        LENGTH(first_name) name_length
FROM
        customer
WHERE 
        first_name LIKE 'A%' AND
        LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY
        name_length;

You will get a table containing all the first names, whose length varies between 3 and 5.

• Using not equal (<>) operator.

SELECT 
        first_name,
        last_name
FROM 
        customer
WHERE 
        first_name LIKE 'Bra%' AND 
        last_name <> 'Motley';

The above query returns a table containing the values that start with ‘Bra’ in the first_name column and all other values except ‘Motley’ in the last_name column.

2. LIMIT

This clause will constrain the number of rows returned by the query.

Syntax:

SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count

The statement returns row_count rows generated by the query. If row_count is zero, the query returns an empty set. In case row_count is NULL, the query returns the same result set as it does not have the LIMIT clause.

Use the OFFSET clause to skip several rows before returning the row_count rows.

SELECT select_list
FROM table_name
LIMIT row_count OFFSET row_to_skip;

• Using LIMIT for limiting the number of rows.

SELECT
        film_id,
        title,
        release_year
FROM
        film
ORDER BY
        film_id
LIMIT 5;

This query returns a table of five rows containing film_id, title, and release_year, where the film_id is ordered in ascending order.

• Using OFFSET.

SELECT
        film_id,
        title,
        release_year
FROM
        film
ORDER BY
        film_id
LIMIT 4 OFFSET 3;

This query returns a table of four rows containing film_id, title, and release_year, where the film_id is ordered in ascending order. It starts the values from film_id = 4 and not from the beginning.

• Using OFFSET for getting top/bottom rows.

SELECT
        film_id,
        title,
        rental_rate
FROM
        film
ORDER BY
        rental_rate DESC
LIMIT 10;

The table with film_id, title, and rental_rate columns contains ten rows, where rental_rate is in the descending order.

3. FETCH

PostgreSQL supports the FETCH clause to retrieve several rows returned by a query.

Syntax:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

The FETCH clause is functionally equivalent to the LIMIT clause. If you plan to make your application compatible with other database systems, you should use the FETCH clause because it follows the standard SQL.

4. IN

Used with the “where” clause, this clause will check whether a value matches any value in a list of values.

value IN (value1,value2,...)

You can also use the select query in place of the values:

value IN (SELECT column_name FROM table_name);

For example:

SELECT customer_id,
        rental_id,
        return_date
FROM
        rental
WHERE
        customer_id IN (1, 2)
ORDER BY
        return_date DESC;

We have 1 and 2 customer IDs, and the above query will return a table containing data of customers with customer ID 1 or 2.

• Using NOT IN

SELECT
        customer_id,
        rental_id,
        return_date
FROM
        rental
WHERE
        customer_id NOT IN (1, 2);

The above query will return a table containing data of all the customers, except the data of customers with customer ID 1 or 2

5. BETWEEN

This clause will match a value against a range of values.

Syntax:

value BETWEEN low AND high;

value >= low and value <= high

value NOT BETWEEN low AND high;

value < low OR value > high

• Using BETWEEN operators

SELECT
        customer_id,
        payment_id,
        amount
FROM
        payment
WHERE
        amount BETWEEN 8 AND 9;

The above query returns the table with customer_id, payment_id, and amount, where the amount ranges between 8 and 9.

• Using NOT BETWEEN

SELECT
        customer_id,
        payment_id,
        amount
FROM
        payment
WHERE
        amount NOT BETWEEN 8 AND 9;

The above query returns the table with customer_id, payment_id, and amount, except for the amount ranging between 8 and 9.

• Using BETWEEN

SELECT
        customer_id,
        payment_id,
        amount,
payment_date
FROM
        payment
WHERE
        payment_date BETWEEN '2007-02-07' AND '2007-02-15';

The above query returns the table containing customer_id, payment_id, amount, and payment_date, where the date ranges between 2007-02-07 and 2007-02-15.

6. LIKE

This operator will match the first name of the customer with a string like this query:

SELECT
        first_name,
       last_name
FROM
        customer
WHERE
        first_name LIKE 'Jen%';

You will get a table with first_name and last_name columns, where the values in the first_name column start with ‘Jen’.

You can match a pattern by combining literal values with wildcard characters and using the LIKE or NOT LIKE operator to find the matches. PostgreSQL provides you with two wildcards:

• Percent sign (%) matches any sequence of zero or more characters.
• Underscore sign (_)  matches any single character.

For example:

SELECT

        'foo' LIKE 'foo', -- true

        'foo' LIKE 'f%', -- true

        'foo' LIKE '_o_', -- true

        'bar' LIKE 'b_'; -- false

• Using (%) and (_)

SELECT
        first_name,
        last_name
FROM
        customer
WHERE
        first_name LIKE '_her%'
ORDER BY 
       First_name;

This returns a table with first_name and last_name columns, where the values in the first_name column start with any letter followed by ‘her’.

• Using NOT LIKE

SELECT
        first_name,
        last_name
FROM
        customer
WHERE
        first_name NOT LIKE 'Jen%'
ORDER BY 
       first_name

You will get a table with first_name and last_name columns, where the first_name column contains values that do not start with ‘Jen.’

• Using ILIKE (checks case sensitivity)

SELECT
        first_name,
        last_name
FROM
        customer
WHERE
        first_name ILIKE 'BAR%';

This query returns a table with first_name and last_name columns, where the values in first_name start with BAR.

7. IS NULL

The IS NULL condition is used to test for the NULL values in SELECT, INSERT, UPDATE, and DELETE statements.

Syntax:

expression IS NULL;

If the expression is NULL, the condition evaluates to true. Otherwise, the condition evaluates as false.

• IS NULL with SELECT.

SELECT *
FROM employees
WHERE first_number is NULL;

The above query returns a table containing records from the employee table whose fisst_number is NULL.

• IS NULL with INSERT

INSERT INTO contacts
(first_name, last_name)
SELECT first_name, last_name
FROM employees
WHERE employee_number IS NULL;

The above query inserts new data into the contacts table whose employee number has a NULL value.

• IS NULL with UPDATE

UPDATE employees
SET status = 'Not Active'
WHERE last_name IS NULL;

The above query updates the records in the employees table whose last name holds a NULL value.

• IS NULL with DELETE

DELETE FROM employees
WHERE employee_number IS NULL;

The above query will delete all the records employees table whose employee number is NULL.

Section 3 - Joining Multiple Tables

Let’s discuss ‘JOIN’ in PostgreSQL:

1. JOINS

You can combine columns from one (self-join) or more tables based on the common column values between the related tables. The common columns are typically the first table’s primary key columns and the second table’s foreign key columns.

To explain the concept, we’ll use two tables where we perform different types of joins.

The following query creates the table ‘basket_a:’

CREATE TABLE basket_a (
   a INT PRIMARY KEY,
   fruit_a VARCHAR (100) NOT NULL
);

The following query creates the table ‘basket_b:’

CREATE TABLE basket_b (
   b INT PRIMARY KEY,
   fruit_b VARCHAR (100) NOT NULL
);

Use the following query to insert data into ‘basket_a:’

INSERT INTO basket_a (a, fruit_a)
VALUES
   (1, 'Apple'),
   (2, 'Orange'),
   (3, 'Banana'),
   (4, 'Cucumber');

The following query inserts data into ‘basket_b:’

INSERT INTO basket_b (b, fruit_b)
VALUES
   (1, 'Orange'),
   (2, 'Apple'),
   (3, 'Watermelon'),
   (4, 'Pear');

• Inner join

SELECT
   a,
   fruit_a,
   b,
   fruit_b
FROM
   basket_a
INNER JOIN basket_b
   ON fruit_a = fruit_b;

• Left join

SELECT
   a,
   fruit_a,
   b,
   fruit_b
FROM
   table_a
LEFT JOIN table_b
  ON fruit_a = fruit_b;

• Right join

SELECT
   a,
   fruit_a,
   b,
   fruit_b
FROM
   table_a
RIGHT JOIN table_b ON fruit_a = fruit_b;

• Full outer join

SELECT
   a,
   fruit_a,
   b,
   fruit_b
FROM
   table_a
FULL OUTER JOIN table_b
   ON fruit_a = fruit_b;

2. Table Alias

These temporarily assign tables new names during the execution of a query.

table_name AS alias_name;

3. Self Join

A self-join is a regular join that joins a table to itself. To form a self-join, you specify the same table twice with different table aliases and provide the join predicate after the ON keyword.

For example, here’s an employee table with some inserted data:

CREATE TABLE employee_data (
        emp_id INT PRIMARY KEY,
        f_name VARCHAR (255) NOT NULL,
        l_name VARCHAR (255) NOT NULL,
        manager_id INT,
        FOREIGN KEY (manager_id)
        REFERENCES employee (emp_id)
        ON DELETE CASCADE
);

INSERT INTO employee (
        emp_id,
        f_name,
        l_name,
        manager_id
)
VALUES
        (1, 'Sam', 'Dunes', NULL),
        (2, 'Ava', 'Mil', 1),
        (3, 'Harry', 'Man, 1),
        (4, 'Aman', 'Deep', 2),
        (5, 'Sunny', 'Rom', 2),
        (6, 'Kelly', 'Hans', 3),
        (7, 'Tony', 'Cliff, 3),
        (8, 'Sam', 'Lanne', 3);

• Using Self Join

SELECT
   e.f_name || ' ' || e.l_name employee,
   m .f_name || ' ' || m .l_name manager
FROM
   employee_data e
INNER JOIN employee_data m ON m .emp_id = e.manager_id
ORDER BY manager;

4. Cross Join

This join allows you to produce a Cartesian Product of rows in two or more tables. Unlike LEFT JOIN  or INNER JOIN, the CROSS JOIN clause does not have a join predicate:

CREATE TABLE table_a (
   a INT PRIMARY KEY,
   fruit_a VARCHAR (100) NOT NULL
);​

CREATE TABLE table_b (
   b INT PRIMARY KEY,
   fruit_b VARCHAR (100) NOT NULL
);​

INSERT INTO table_a (a, fruit_a)
VALUES
   (1, 'Apple'),
   (2, 'Orange'),
   (3, 'Melon'),
   (4, 'Carrot');​

INSERT INTO table_b (b, fruit_b)
VALUES
   (1, 'Orange'),
   (2, 'Apple'),
   (3, 'Berry'),
   (4, 'Raddish');

SELECT * FROM table_a cross join table_b;

5. Natural Join

This creates an implicit join based on the same column names in the joined tables.

Syntax:

SELECT select_list
FROM T1
NATURAL [INNER, LEFT, RIGHT] JOIN T2;

For example, we have created a table and inserted some rows:

DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
        cat_id serial PRIMARY KEY,
        cat_name VARCHAR (255) NOT NULL
);​

DROP TABLE IF EXISTS products;
CREATE TABLE prod (
        prod_id serial PRIMARY KEY,
        prod_name VARCHAR (255) NOT NULL,
        cat_id INT NOT NULL,
        FOREIGN KEY (cat_id) REFERENCES categories (cat_id)
);​

INSERT INTO categories (cat_name)
VALUES
        ('Smart Phone'),
        ('Laptop'),
        ('Tablet');​

INSERT INTO prod (prod_name, cat_id)
VALUES
        ('iPhone', 1),
        ('Samsung Galaxy', 1),
        ('HP Elite', 2),
        ('Lenovo Thinkpad', 2),
        ('iPad', 3),
        ('Kindle Fire', 3);

• Using natural join

SELECT * FROM prod
NATURAL JOIN categories;

Section 4 - Grouping Data

1. Group by Clause

This divides the rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function.

Syntax:

SELECT 
  column_1,
  column_2,
  ...,
  aggregate_function(column_3)
FROM 
  table_name
GROUP BY 
  column_1,
  column_2,
  ...;

PostgreSQL evaluates the GROUP BY clause after the FROM and WHERE clauses and before the HAVING SELECT, DISTINCT, ORDER BY, and LIMIT clauses.

DROP TABLE IF EXISTS employee_data;

CREATE TABLE employee_data (
        emp_id INT PRIMARY KEY,
        f_name VARCHAR (255) NOT NULL,
        l_name VARCHAR (255) NOT NULL,
        manager_id INT,
        Salary int,
        FOREIGN KEY (manager_id)
        REFERENCES employee_data (emp_id)
        ON DELETE CASCADE
);​

INSERT INTO employee_data (
        emp_id,
        f_name,
        L_name,
        salary,
        manager_id
)
VALUES
        (1, 'Sam', 'Dunes', 1000,NULL),
        (2, 'Ava', 'Mil', 2000, 1),
        (3, 'Harry', 'Man', 2400, 1),
        (4, 'Aman', 'Deep', 4500, 2),
        (5, 'Sunny', 'Rom', 3455, 2),
        (6, 'Kelly', 'Hans', 6733,  3),
        (7, 'Tony', 'Cliff', 4577, 3),
        (8, 'Sam', 'Lanne', 4533, 3);

• Using group by without aggregate function

SELECT
  emp_id
FROM
  employee_data
GROUP BY
  Manager_id;

You will get an error:

column "employee_data.emp_id" must appear in the GROUP BY clause or be used in an aggregate function.

• Group by with sum() function

SELECT
        emp_id,
        SUM (salary)
FROM
        employee_data
GROUP BY
        emp_id;

• Using group by with join clause

SELECT
        f_name || ' ' || l_name full_name,
        SUM (salary) amount
FROM
        employee_data           
GROUP BY
        full_name, employee_data.salary
ORDER BY salary DESC;        

• Group by with count() function

SELECT
        emp_id,
        COUNT (manager_id)
FROM
        employee_data
GROUP BY
        manager_id;

• Group by with multiple columns

SELECT 
        emp_id,
        manager_id,
        SUM(salary)
FROM 
        employee_data
GROUP BY 
        manager_id,
        emp_id
ORDER BY 
   emp_id;

• Group by with date column

SELECT 
                emp_id, manager_id, SUM(salary) sum
FROM 
        employee_data
GROUP BY
        emp_id, manager_id,salary ;

Having Clause

This specifies a search condition for a group or an aggregate. The HAVING clause is often used with the GROUP BY clause to filter groups or aggregates based on a specified condition.

Syntax:

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

PostgreSQL evaluates the HAVING clause after the FROM, WHERE, GROUP BY, and before the SELECT, DISTINCT, ORDER BY, and LIMIT clauses.

• Using having with a sum function

SELECT
        EMP_id,
        SUM (SALARY)
FROM
        EMPLOYEE_DATA
GROUP BY
        emp_id;

• Using the having clause with the count function

SELECT
        manager_id,
        COUNT (emp_id)
FROM
        employee_data
GROUP BY
        manager_id;

Section 5 - Set Operations

This section will walk you through different set operations supported by PostgreSQL.

1. UNION

It combines result sets of two or more SELECT statements into a single result set.

Syntax:

SELECT select_list_1
FROM table_expresssion_1
UNION
SELECT select_list_2
FROM table_expression_2

For example, we have created two tables and inserted data in them to perform union.

DROP TABLE IF EXISTS top_films;
CREATE TABLE top_films(
        title VARCHAR NOT NULL,
        release_year SMALLINT
);​

DROP TABLE IF EXISTS popular_films;
CREATE TABLE popular_films(
        title VARCHAR NOT NULL,
        release_year SMALLINT
);​

INSERT INTO 
  top_films(title,release_year)
VALUES
  ('hello',1994),
  ('The Godfather',1972),
  ('james bond',1957);​

INSERT INTO 
  popular_films(title,release_year)
VALUES
  ('shore',2020),
  ('The Godfather',1972),
  ('mickey mouse,2020);​

SELECT * FROM top_films;

SELECT * FROM popular_films;

• Union example

SELECT * FROM top_films
UNION
SELECT * FROM popular_films;

• Union all example

SELECT * FROM top_films
UNION ALL
SELECT * FROM popular_films;

• Union all with order by

SELECT * FROM top_films
UNION ALL
SELECT * FROM popular_films
ORDER BY title;

2. INTERSECT

This operator combines result sets of two or more SELECT statements into a single result set.

Syntax:

SELECT select_list
FROM A
INTERSECT
SELECT select_list
FROM B;

Make sure the number of columns is the same and have compatible data types to be merged.

• Intersect with an order by clause

SELECT select_list
FROM A
INTERSECT
SELECT select_list
FROM B
ORDER BY sort_expression;

For example, we will intersect the two tables: popular_films and top_films:

SELECT *
FROM popular_films
INTERSECT
SELECT *
FROM top_films;

Section 6 - Grouping Sets, Cube, and Rollup

1. Grouping Sets

This can generate multiple grouping sets in a query. To explain, we will create the sales table:

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
   brand VARCHAR NOT NULL,
   segment VARCHAR NOT NULL,
   quantity INT NOT NULL,
   PRIMARY KEY (brand, segment)
);​

INSERT INTO sales (brand, segment, quantity)
VALUES
   ('zara', 'Premium', 100),
   ('hnm', 'Basic', 200),
   ('aldo', 'Premium', 100),
   ('baggit', 'Basic', 300);​

SELECT * FROM sales;

A grouping set is a set of columns that you group using the GROUP BY clause. The grouping sets’ syntax consists of multiple columns enclosed in parentheses, separated by commas.

Syntax:

(column1, column2, ...)

SELECT
   brand,
   segment,
   SUM (quantity)
FROM
   sales
GROUP BY
   brand,
   segment;

SELECT
   brand,
   SUM (quantity)
FROM
   sales
GROUP BY
   brand;

SELECT
   segment,
   SUM (quantity)
FROM
   sales
GROUP BY
   segment;

General syntax of grouping sets

SELECT
   c1,
   c2,
   aggregate_function(c3)
FROM
   table_name
GROUP BY
   GROUPING SETS (
       (c1, c2),
       (c1),
       (c2),
       ()
);

For example:

SELECT
   brand,
   segment,
   SUM (quantity)
FROM
   sales
GROUP BY
   GROUPING SETS (
       (brand, segment),
       (brand),
       (segment),
       ()
   );

2. CUBE

CUBE is a subclause of the GROUP BY clause. The CUBE allows you to generate multiple grouping sets.

Syntax:

SELECT
   c1,
   c2,
   c3,
   aggregate (c4)
FROM
   table_name
GROUP BY
   CUBE (c1, c2, c3);

The CUBE subclause is a short way to define multiple grouping sets, so the following two queries are equivalent.

CUBE(c1,c2,c3)

GROUPING SETS (
   (c1,c2,c3),
   (c1,c2),
   (c1,c3),
   (c2,c3),
   (c1),
   (c2),
   (c3),
   ()
)

We will use the ‘sales’ table to understand the CUBE clause:

SELECT * FROM sales;

SELECT
   brand,
   segment,
   SUM (quantity)
FROM
   sales
GROUP BY
   CUBE (brand, segment)
ORDER BY
   brand,
   segment;

• Partial cube

SELECT
   brand,
   segment,
   SUM (quantity)
FROM
   sales
GROUP BY
   brand,
   CUBE (segment)
ORDER BY
   brand,
   segment;

3. Roll-Up

ROLLUP is a subclause of the GROUP BY clause that offers a shorthand for defining multiple grouping sets. Unlike the CUBE subclause, ROLLUP does not generate all possible grouping sets based on the specified columns. It just makes a subset.

Syntax:

SELECT
   c1,
   c2,
   c3,
   aggregate(c4)
FROM
   table_name
GROUP BY
   ROLLUP (c1, c2, c3);

• Partial roll-up syntax

SELECT
   c1,
   c2,
   c3,
   aggregate(c4)
FROM
   table_name
GROUP BY
   c1,
   ROLLUP (c2, c3);

For example, we have created the ‘sales’ table and inserted data:

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
   brand VARCHAR NOT NULL,
   segment VARCHAR NOT NULL,
   quantity INT NOT NULL,
   PRIMARY KEY (brand, segment)
);​

INSERT INTO sales (brand, segment, quantity)
VALUES
   ('zara', 'Premium', 100),
   ('hnm', 'Basic', 200),
   ('aldo', 'Premium', 100),
   ('baggit', 'Basic', 300);

SELECT * FROM sales:

• Roll-up example

SELECT
   brand,
   segment,
   SUM (quantity)
FROM
   sales
GROUP BY
   ROLLUP (brand, segment)
ORDER BY
   brand,
   segment;

• Partial roll-up example

SELECT
   segment,
   brand,
   SUM (quantity)
FROM
   sales
GROUP BY
   segment,
   ROLLUP (brand)
ORDER BY
   segment,
   brand;

Section 7 - Subquery

1. Subquery

Retrieving a specific output sometimes requires running more than one query. To reduce the steps, we use the subquery — a query inside another query.

Suppose we want to know the movies whose rental rate is greater than the average. We will create the ‘film’ table and insert values into it:

DROP TABLE IF EXISTS film;
CREATE TABLE film (
   film_id INT NOT NULL,
   film_title VARCHAR NOT NULL,
   rental_rate INT NOT NULL,
 
   PRIMARY KEY (film_id)
);​

INSERT INTO film (film_id, film_title, rental_rate)
VALUES
   (1, 'hunny', 100),
   (2, 'block', 200),
   (3, 'james bond', 300),
   (4, 'sunny', 400);​

SELECT * FROM film;

SELECT
        AVG (rental_rate)
FROM
        film;

SELECT
        film_id,
        film_title,
        rental_rate
FROM
        film
WHERE
        rental_rate > 2.98;

We use the subquery:

SELECT
        film_id,
        film_title,
        rental_rate
FROM
        film
WHERE
        rental_rate > (
                SELECT
                        AVG (rental_rate)
                FROM
                        film
        );

• Subquery with IN operator

SELECT
        film_id
FROM
        film
WHERE
        salary BETWEEN '100'
AND '300';

The above example will only allow a single column output. To get multiple columns, use the subquery:

SELECT
        film_id,
        film_title
FROM
        film
WHERE
        film_id IN (
                SELECT
                        film_id
                FROM
                        film
                                WHERE
                        rental_rate BETWEEN '100'
                AND '400'
        );

2. ANY

The ANY operator compares a value to a set of values returned by a subquery. It returns true if any subquery value meets the condition; otherwise, it returns false.

Syntax:

expression operator ANY(subquery)

For example, find the films whose lengths are greater than or equal to the maximum length of any film category:

SELECT film_title
FROM film
WHERE rental_rate >= ANY(
   SELECT MAX( rental_rate )
   FROM film
      );

3. ALL

The ALL operator allows you to query data by comparing a value with a list of values returned by a subquery.

Syntax:

comparison_operator ALL (subquery)

To find the average lengths of all films grouped by film rating, run the following query:

SELECT
   ROUND(AVG(rental_rate), 2) avg_rate
FROM
   film
GROUP BY
   film_id
ORDER BY
   avg_rate DESC;

Find all films whose lengths are greater than the list of the average lengths above:

SELECT
   film_id,
   film_title,
   rental_rate
FROM
   film
WHERE
   rental_rate <= ALL (
           SELECT
               ROUND(AVG (rental_rate),2)
           FROM
               film
           GROUP BY
               film_id
   )
ORDER BY
   rental_rate;

4. EXISTS

This is a boolean operator that tests for the existence of rows in a subquery.

Syntax:

SELECT 
   column1
FROM 
   table_1
WHERE 
   EXISTS( SELECT 
               1 
           FROM 
               table_2
           WHERE 
               column_2 = table_1.column_1);

It will accept the subquery as an argument. If the subquery returns at least one row, the result of EXISTS is true. In case the subquery returns no row, the result of EXISTS is false. The EXISTS operator is often used with the correlated subquery.

• Customers with at least one payment whose amount is greater than 11

SELECT f_name,
      l_name
FROM employee_data
WHERE EXISTS
   (SELECT 1
    FROM employee_data
    WHERE salary > 2000 )
ORDER BY f_name,
        l_name;

• NOT EXISTS

SELECT f_name,
      l_name
FROM employee_data
WHERE NOT EXISTS
   (SELECT 1
    FROM employee_data
    WHERE salary < 3000)
ORDER BY f_name,
        l_name;

No result set.

Section 8 - Common Table Expressions

A common table expression is a temporary result set that you can reference within another SQL statement, including SELECT, INSERT, UPDATE, or DELETE.  Common table expressions are temporary as they only exist during the query’s execution.

Syntax:

WITH cte_name (column_list) AS (
   CTE_query_definition
)
statement;

For example:

WITH cte_film AS (
   SELECT 
       film_id,
       Film_title, rental_rate
         FROM
       film
)​

SELECT
   film_id,
   film_title,
   rental_rate
FROM 
   cte_film
WHERE
 rental_rate>200
ORDER BY 
   film_title;

• Joining CTE with table

WITH cte_emp AS (
   SELECT emp_id,
       COUNT(salary) count
   FROM   employee_data
   GROUP  BY manager_id, emp_id
)
SELECT emp_id,
   f_name,
   l_name,
   salary
FROM employee_data;

1. Recursive Query

A recursive query refers to a recursive CTE. They’re useful in querying hierarchical data like organizational structure, bill of materials, etc.

Syntax:

WITH RECURSIVE cte_name AS(
   CTE_query_definition -- non-recursive term
   UNION [ALL]
   CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

For example, we have created the ‘employees’ table and inserted some data:

CREATE TABLE employees (
        employee_id serial PRIMARY KEY,
        full_name VARCHAR NOT NULL,
        manager_id INT
);​

INSERT INTO employees (
        employee_id,
        full_name,
        manager_id
)
VALUES
        (1, 'Michael North', NULL),
        (2, 'Megan Berry', 1),
        (3, 'Sarah Berry', 1),
        (4, 'Zoe Black', 1),
        (5, 'Tim James', 1),
        (6, 'Bella Tucker', 2),
        (7, 'Ryan Metcalfe', 2),
        (8, 'Max Mills', 2),
        (9, 'Benjamin Glover', 2),
        (10, 'Carolyn Henderson', 3),
        (11, 'Nicola Kelly', 3),
        (12, 'Alexandra Climo', 3),
        (13, 'Dominic King', 3),
        (14, 'Leonard Gray', 4),
        (15, 'Eric Rampling', 4),
        (16, 'Piers Paige', 7),
        (17, 'Ryan Henderson', 7),
        (18, 'Frank Tucker', 8),
        (19, 'Nathan Ferguson', 8),
        (20, 'Kevin Rampling', 8);

To get all manager subordinates with the id 2:

WITH RECURSIVE subordinates AS (
        SELECT
                employee_id,
                manager_id,
                full_name
        FROM
                employees
        WHERE
                employee_id = 2
        UNION
                SELECT
                        e.employee_id,
                        e.manager_id,
                        e.full_name
                FROM
                        employees e
                INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
        *
FROM
        subordinates;

The non-recursive term returns the base result set R0, the employee with the id 2.

 employee_id | manager_id |  full_name

-------------+------------+-------------

           2 |          1 | Megan Berry

The recursive term’s first iteration returns the following result set:

 employee_id | manager_id |    full_name

-------------+------------+-----------------

           6 |          2 | Bella Tucker

           7 |          2 | Ryan Metcalfe

           8 |          2 | Max Mills

           9 |          2 | Benjamin Glover

The second iteration of the recursive member uses the result set above step as the input value, and returns this result set.

 employee_id | manager_id |    full_name

-------------+------------+-----------------

          16 |          7 | Piers Paige

          17 |          7 | Ryan Henderson

          18 |          8 | Frank Tucker

          19 |          8 | Nathan Ferguson

          20 |          8 | Kevin Rampling

The third iteration returns an empty result set as no employee reports to the employee with id 16, 17, 18, 19, and 20.

The following is the final result set, which is the union of all result sets in the first and second iterations generated by the non-recursive and recursive terms.

Section 9 - Modifying Data

In this section, you will learn how to:

• insert data into a table with the INSERT statement
• modify existing data with the UPDATE statement
• remove data with the DELETE statement
• merge data with the UPSERT statement

Insert

This statement allows you to insert a new row into a table.

Syntax:

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

• Returning clause

If you want to return the entire inserted row, you use an asterisk (*) after the RETURNING keyword.

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

To return some information about the inserted row, you can specify one or more columns after the RETURNING clause.

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

• Inserting single row

INSERT INTO links (url, name)
VALUES('https://www.xyz.com', 'data');

• Inserting character strings containing singgleute

INSERT INTO links (url, name)
VALUES('http://www.xyz.com','O''XYZ');

• Insert date value

INSERT INTO links (url, name, last_update)
VALUES('https://www.google.com','Google','2013-06-01');

• Getting the last insert id

INSERT INTO links (url, name)
VALUES('http://www.xyz.org','PostgreSQL')
RETURNING id;

INSERT Multiple Rows

Use the following syntax to insert multiple rows.

INSERT INTO table_name (column_list)
VALUES
   (value_list_1),
   (value_list_2),
   ...
   (value_list_n);

To insert and return multiple rows, use the returning clause:

INSERT INTO table_name (column_list)
VALUES
   (value_list_1),
   (value_list_2),
   ...
   (value_list_n)
RETURNING * | output_expression;

For example, we will create the ‘links’ table and insert data:

DROP TABLE IF EXISTS links;

CREATE TABLE links (
   id SERIAL PRIMARY KEY,
   url VARCHAR(255) NOT NULL,
   name VARCHAR(255) NOT NULL,
   description VARCHAR(255)
);

• Insert multiple rows using the below query

INSERT INTO 
   links (url, name)
VALUES
   ('https://www.google.com','Google'),
   ('https://www.yahoo.com','Yahoo'),
   ('https://www.bing.com','Bing');

SELECT * FROM links;

• Insert and return multiple columns

INSERT INTO 
   links(url,name, description)
VALUES
   ('https://duckduckgo.com/','DuckDuckGo','Privacy & Simplified Search Engine'),
   ('https://swisscows.com/','Swisscows','Privacy safe WEB-search')
RETURNING *;​

SELECT * FROM links;

Update

UPDATE allows you to modify data in a table.

Syntax:

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

• Return updated rows

UPDATE table_name
SET column1 = value1,
   column2 = value2,
   ...
WHERE condition
RETURNING * | output_expression AS output_name;

To explain, we will create the ‘courses’ table and insert data:

DROP TABLE IF EXISTS courses;

CREATE TABLE courses(
        course_id serial primary key,
        course_name VARCHAR(255) NOT NULL,
        description VARCHAR(500),
        published_date date
);​

INSERT INTO 
        courses(course_name, description, published_date)
VALUES
        ('PostgreSQL for Developers','A complete PostgreSQL for Developers','2020-07-13'),
        ('PostgreSQL Admininstration','A PostgreSQL Guide for DBA',NULL),
        ('PostgreSQL High Performance',NULL,NULL),
        ('PostgreSQL Bootcamp','Learn PostgreSQL via Bootcamp','2013-07-11'),
        ('Mastering PostgreSQL','Mastering PostgreSQL in 21 Days','2012-06-30');

SELECT * FROM courses:

• Update a single row

UPDATE courses
SET published_date = '2020-08-01' 
WHERE course_id = 3;​

SELECT * FROM COURSES WHERE COURSE_ID=3;

Update Join

Use this statement to update data in a table based on values in another table.

Syntax:

UPDATE t1
SET t1.c1 = new_value
FROM t2
WHERE t1.c2 = t2.c2;

For example, we have created two tables and inserted data into it.

–Table 1

CREATE TABLE prod (
   id SERIAL PRIMARY KEY,
   segment VARCHAR NOT NULL,
   discount NUMERIC (4, 2)
);

INSERT INTO 
   Prod (segment, discount)
VALUES
   ('Grand Luxury', 0.05),
   ('Luxury', 0.06),
   ('Mass', 0.1);

–Table 2

DROP TABLE IF EXISTS product;
CREATE TABLE product(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL,
   price NUMERIC(10,2),
   net_price NUMERIC(10,2),
   segment_id INT NOT NULL,
   FOREIGN KEY(segment_id) REFERENCES prod(id)
);

INSERT INTO 
   product (name, price, segment_id)
VALUES 
   ('diam', 804.89, 1),
   ('vestibulum aliquet', 228.55, 3),
   ('lacinia erat', 366.45, 2),
   ('scelerisque quam turpis', 145.33, 3),
   ('justo lacinia', 551.77, 2),
   ('ultrices mattis odio', 261.58, 3),
   ('hendrerit', 519.62, 2),
   ('in hac habitasse', 843.31, 1)
  ;

Now, we’ll run an update join query:

UPDATE product
SET net_price = price - price * discount
FROM prod
WHERE product.segment_id = prod.id;

SELECT * FROM product;

Delete

This statement allows you to delete one or more rows from a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Use the RETURNING clause to return the deleted row(s) to the client:

DELETE FROM table_name
WHERE condition
RETURNING (select_list | *)

• Deleting one row

DELETE FROM links
WHERE id = 8;

• Deleting and returning one row.

DELETE FROM links
WHERE id = 7
RETURNING *;

• Deleting multiple rows

DELETE FROM links
WHERE id IN (6,5)
RETURNING *;

UPSERT

This statement will insert or update data into an existing row. The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists; otherwise, it will insert the new row. That is why we call the action upsert.

To use the upsert feature in PostgreSQL, use the INSERT ON CONFLICT statement.

INSERT INTO table_name(column_list)
VALUES(value_list)
ON CONFLICT target action;

For example, we will create the ‘customers’ table and insert data into it:

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
        cust_id serial PRIMARY KEY,
        name VARCHAR UNIQUE,
        email VARCHAR NOT NULL,
        active bool NOT NULL DEFAULT TRUE
);​

INSERT INTO 
   customers (name, email)
VALUES 
   ('sam', 'sam@ibm.com'),
   ('Microsoft', 'contact@microsoft.com'),
   ('Harry', 'harry@intel.com');

Now we can use the following query to change the email:

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com')
ON CONFLICT (name)
DO 
  UPDATE SET email = EXCLUDED.email || ';' || customers.email;

Section 10 - Transactions

A database transaction is a single unit of work with several operations. A PostgreSQL transaction is atomic, consistent, isolated, and durable, represented as ACID properties.

For example:

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
   id INT GENERATED BY DEFAULT AS IDENTITY,
   name VARCHAR(100) NOT NULL,
   balance DEC(15,2) NOT NULL,
   PRIMARY KEY(id)
);

• Begin a transaction

Use the BEGIN statement at the start of your statements.

BEGIN;

INSERT INTO accounts(name,balance)
VALUES('Alice',10000);

• Commit a transaction.

This makes your changes permanent, even if a crash happens.

-- Start a transaction.

BEGIN;

-- Insert a new row into the accounts table.

INSERT INTO accounts(name,balance)
VALUES('Alice',10000);

-- Commit the change (or roll it back later).

COMMIT;

• Rollback a transaction.

You can rollback any transaction until the last commit statement.

-- Begin the transaction.

BEGIN;

-- Deduct the amount from account 1.

UPDATE accounts
SET balance = balance - 1500
WHERE id = 1;

-- Add the amount from account 3 (instead of 2).

UPDATE accounts
SET balance = balance + 1500
WHERE id = 3;

-- Roll back the transaction.

ROLLBACK;

Section 11 - Import CSV File Into PostgreSQL Table

We will now create the ‘persons’ table to understand how to import a CSV file into a PostgreSQL table.

CREATE TABLE persons (
 id SERIAL,
 f_name VARCHAR(50),
 l_name VARCHAR(50),
 dob DATE,
 email VARCHAR(255),
 PRIMARY KEY (id)
)

Create a CSV with the following format:

• Importing with the copy statement

COPY persons(first_name, last_name, dob, email)
FROM 'C:\sampledb\persons.csv'
DELIMITER ','
CSV HEADER;​

SELECT * FROM persons;

• Using pgAdmin

The following query will truncate the table to start again:

TRUNCATE TABLE persons
RESTART IDENTITY;

Section 12 - Managing Tables in PostgreSQL

Data Types

• Boolean: Can hold one of three possible values: true, false or null.
• CHAR(n): the fixed-length character with space padded.
• ARCHAR(n): variable-length character string and can store upto n characters.
• TEXT: variable-length character string.
• Small integer ( SMALLINT): 2-byte signed integer that ranges from -32,768 to 32,767.
• Integer ( INT): 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
• float(n): floating-point number whose precision, at least, n, up to a maximum of 8 bytes.
• realor float8: 4-byte floating-point number.
• numeric or numeric(p,s): a real number with p digits with s number after the decimal point. The numeric(p,s) is the exact number.
• DATE: stores the dates only.
• TIME: stores the time of day values.
• TIMESTAMP: stores both date and time values.
• TIMESTAMPTZ: timezone-aware timestamp data type. It is the abbreviation for timestamp with the time zone.
• INTERVAL: stores periods of time.

Create Table

Use the CREATE TABLE statement to create a new table:

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name (
  column1 datatype(length) column_contraint,
  column2 datatype(length) column_contraint,
  column3 datatype(length) column_contraint,
  table_constraints
);

Select Into

This statement creates a new table and inserts data returned from a query into the table. The new table will have columns with the same names as the query’s result set columns. Unlike a regular SELECT statement, the SELECT INTO statement does not return a result to the client.

Syntax:

SELECT
   select_list
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table_name
FROM
   table_name
WHERE
   search_condition;

For example:

SELECT
   film_id,
  film_ title,
   rental_rate
INTO TABLE film_r
FROM
   film
WHERE
  Rental_rate > 200
ORDER BY
  film_ title;​

SELECT * FROM film_r;

Sequence

A sequence in PostgreSQL is a user-defined schema-bound object that generates a sequence of integers based on a specified specification.

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
   [ AS { SMALLINT | INT | BIGINT } ]
   [ INCREMENT [ BY ] increment ]
   [ MINVALUE minvalue | NO MINVALUE ]
   [ MAXVALUE maxvalue | NO MAXVALUE ]
   [ START [ WITH ] start ]
   [ CACHE cache ]
   [ [ NO ] CYCLE ]
   [ OWNED BY { table_name.column_name | NONE } ]

• Creating an ascending sequence

CREATE SEQUENCE mysequence
INCREMENT 5
START 100;

• Creating a descending sequence

CREATE SEQUENCE three
INCREMENT -1
MINVALUE 1 
MAXVALUE 3
START 3
CYCLE;

• Listing all sequences

SELECT
   relname sequence_name
FROM 
   pg_class
WHERE 
   relkind = 'S';

• Deleting sequence

DROP SEQUENCE [ IF EXISTS ] sequence_name [, ...]
[ CASCADE | RESTRICT ];

• Drop sequence

DROP TABLE order_details;

Identity Column

It allows you to automatically assign a unique number to a column. The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good old SERIAL column.

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

• Generated always examples

CREATE TABLE color (
   color_id INT GENERATED ALWAYS AS IDENTITY,
   color_name VARCHAR NOT NULL
);​

INSERT INTO color(color_name)
VALUES ('Red');

• Generated by default as identity

DROP TABLE color;

CREATE TABLE color (
   color_id INT GENERATED BY DEFAULT AS IDENTITY,
   color_name VARCHAR NOT NULL
);​

INSERT INTO color (color_name)
VALUES ('White');

Alter Table

To change the structure of an existing table, use the PostgreSQL ALTER TABLE statement.

ALTER TABLE table_name action;

• Adding a new column

ALTER TABLE table_name
ADD COLUMN column_name datatype column_constraint;

• Dropping a column

ALTER TABLE table_name
ADD COLUMN column_name datatype column_constraint;

• Renaming a column

ALTER TABLE table_name
RENAME COLUMN column_name
TO new_column_name;

• Changing default value of column

ALTER TABLE table_name
ALTER COLUMN column_name
[SET DEFAULT value | DROP DEFAULT];

• Changing not null constraint

ALTER TABLE table_name
ALTER COLUMN column_name
[SET NOT NULL| DROP NOT NULL];

• Adding check constraint

ALTER TABLE table_name
ADD CHECK expression;

• Renaming a table

ALTER TABLE table_name
RENAME TO new_table_name;

Drop Table

Use the following syntax to drop a table:

DROP TABLE [IF EXISTS] table_name
[CASCADE | RESTRICT];

• The CASCADE option allows you to remove the table and its dependent objects.
• The RESTRICT option rejects the removal if there is any object, depending on the table. This is the default if you don’t explicitly specify it in the DROP TABLE statement.

You can also drop multiple tables separated by commas:

DROP TABLE [IF EXISTS]
  table_name_1,
  table_name_2,
  ...
[CASCADE | RESTRICT];

• Dropping table that does not exist

DROP TABLE IF EXISTS author;

• Dropping table with dependent objects

CREATE TABLE authors (
       firstname VARCHAR (50),
          author_id INT PRIMARY KEY,
       lastname VARCHAR (50)
);​

CREATE TABLE pages (
        page_id serial PRIMARY KEY,
        title VARCHAR (255) NOT NULL,
        contents TEXT,
        author_id INT NOT NULL,
        FOREIGN KEY (author_id)
         REFERENCES authors (author_id)
);
DROP TABLE IF EXISTS authors;

ERROR:  cannot drop table authors because other objects depend on it

DETAIL:  constraint pages_author_id_fkey on table pages depends on table authors

HINT:  Use DROP ... CASCADE to drop the dependent objects too

SQL state: 2BP01

Run the following query:

DROP TABLE authors CASCADE;

• Dropping multiple tables

DROP TABLE tv shows, animes;

Truncate Table

To remove all data from a table, you use the DELETE statement. However, you might use the TRUNCATE TABLE statement for more efficiency.

TRUNCATE TABLE table_name;

• Truncating multiple tables

TRUNCATE TABLE 
   table_name1,
   table_name2,
   ...;

• Truncating table with foreign key references

TRUNCATE TABLE table_name
CASCADE;

Temporary Table

A temporary table is a short-lived table that exists for the duration of a database session. PostgreSQL automatically drops the temporary tables at the end of a session or a transaction.

CREATE TEMPORARY TABLE temp_table_name(
  column_list
);

Section 13 - PostgreSQL Constraints

PostgreSQL includes the following column constraints:

• NOT NULL: Ensures that values in a column cannot be NULL.

CREATE TABLE table_name(
  ...
  column_name data_type NOT NULL,
  ...
);

• UNIQUE: Ensures values in a column are unique across the rows within the same table.

CREATE TABLE person (
        id SERIAL PRIMARY KEY,
        first_name VARCHAR (50),
        last_name VARCHAR (50),
        email VARCHAR (50) UNIQUE
);

• PRIMARY KEY: Identifies a table’s primary key.

CREATE TABLE po_headers (
        po_no INTEGER PRIMARY KEY,
        vendor_no INTEGER,
        description TEXT,
        shipping_address TEXT
);

• CHECK: Ensures the data satisfies a boolean expression.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
        id SERIAL PRIMARY KEY,
        first_name VARCHAR (50),
        last_name VARCHAR (50),
        birth_date DATE CHECK (birth_date > '1900-01-01'),
        joined_date DATE CHECK (joined_date > birth_date),
        salary numeric CHECK(salary > 0)
);

• FOREIGN KEY: Ensures values in a column or a group of columns from a table exists in a column or group of columns in another table. Unlike the primary key, a table can have many foreign keys.

[CONSTRAINT fk_name]
  FOREIGN KEY(fk_columns)
  REFERENCES parent_table(parent_key_columns)
  [ON DELETE delete_action]
  [ON UPDATE update_action]

Section 14 - Conditional Expressions & Operators

CASE

The CASE expression is the same as IF/ELSE statement in other programming languages. It allows you to add if-else logic to the query to form a powerful query.

CASE
     WHEN condition_1  THEN result_1
     WHEN condition_2  THEN result_2
     [WHEN ...]
     [ELSE else_result]
END

COALESCE

The COALESCE function accepts an unlimited number of arguments. It returns the first argument that is not null. If all arguments are null, the COALESCE function will return null.

This function evaluates arguments from left to right until it finds the first non-null argument. All the remaining arguments from the first non-null argument are not evaluated.

Syntax:

COALESCE (argument_1, argument_2, ...);

For example:

CREATE TABLE items (
        ID serial PRIMARY KEY,
        product VARCHAR (100) NOT NULL,
        price NUMERIC NOT NULL,
        discount NUMERIC
);​

INSERT INTO items (product, price, discount)
VALUES
        ('A', 1000 ,10),
        ('B', 1500 ,20),
        ('C', 800 ,5),
        ('D', 500, NULL);

SELECT
        product,
        (price - COALESCE(discount,0)) AS net_price
FROM
        items;

NULLIF

The NULLIF function is one of the most common conditional PostgreSQL expressions.

Syntax:

SELECT
        NULLIF (1, 1); -- return NULL

SELECT
        NULLIF (1, 0); -- return 1

SELECT
        NULLIF ('A', 'B'); -- return A

For example:

CREATE TABLE posts (
 id serial primary key,
        title VARCHAR (255) NOT NULL,
        excerpt VARCHAR (150),
        body TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP
);​

INSERT INTO posts (title, excerpt, body)
VALUES
     ('test post 1','test post excerpt 1','test post body 1'),
     ('test post 2','','test post body 2'),
     ('test post 3', null ,'test post body 3');​

SELECT
        id,
        title,
        COALESCE (excerpt, LEFT(body, 40))
FROM
        posts;

CAST

There are many cases where you’d want to convert a value from one data type to another. PostgreSQL provides you with the CAST operator that allows you to do this.

Syntax:

CAST ( expression AS target_type );

• Casting string to integer

SELECT
        CAST ('100' AS INTEGER);

• Casting a string to date

SELECT
  CAST ('2015-01-01' AS DATE),
  CAST ('01-OCT-2015' AS DATE);

• Casting string to double

SELECT
        CAST ('10.2' AS DOUBLE);

• Casting string to boolean

SELECT 
  CAST('true' AS BOOLEAN),
  CAST('false' as BOOLEAN),
  CAST('T' as BOOLEAN),
  CAST('F' as BOOLEAN);

• Converting string to timestamp

SELECT '2019-06-15 14:30:20'::timestamp;

• Converting string to an interval

SELECT '15 minute'::interval,
'2 hour'::interval,
'1 day'::interval,
'2 week'::interval,
'3 month'::interval;

Section 15 - Psql Commands Cheat Sheet

Here are the most commonly used Psql commands:

• Connect to PostgreSQL

psql -d database -U  user -W

• Switch to a new database

\c dbname username

• List available databases

\l

• List available tables

\dt

• Describe table

\d table_name

• List available schema

\dn

• List available views

\dv

• List users and their roles

\du

• Execute previous commands

SELECT version();

• Command history

\s

• Execute psql from a file

\i filename

• Get help

\?

• Turn on query execution time

dvdrental=# \timing

Timing is on.

dvdrental=# select count(*) from film;

 count

-------

  1000

(1 row)

Time: 1.495 ms

dvdrental=#

• Quit plsql

\q

Conclusion

In this PostgreSQL cheat sheet, we’ve covered all of PostgreSQL’s basic concepts and a quick PostgreSQL commands cheat sheet. Whether you’re a beginner or professional, you can find excellent use for this cheat sheet in your day-to-day programming.

Curious about other programming cheat sheets? Explore our Java Cheat Sheet!

Frequently Asked Questions

1. What is the Best Way to Learn PostgreSQL?

There are several online resources that you can consider:

• PostgreSQL for Developers- A complete PostgreSQL for Developers
• PostgreSQL Administration- A PostgreSQL Guide for DBA
• PostgreSQL High Performance
• PostgreSQL Bootcamp- Learn PostgreSQL via Bootcamp
• Mastering PostgreSQL

2. What Does '# Mean in Psql?

The "postgres=#" prompt is a fresh prompt waiting for the start of a new command. The "postgres-#" prompt is the result of hitting enter after typing a command that does not end with a semicolon.

3. How Do I See All Tables in PostgreSQL?

Using SQL Query

Run the following query:

SELECT * FROM information_schema.tables;

or in a particular schema:

SELECT * FROM information_schema.tables WHERE table_schema = 'schema_name';

Using Psql

To list all tables:

In all schemas: \dt *.*

In a particular schema: \dt schema_name.*

4. Is Postgres Faster than MySQL?

Speed matters in deciding which database to use. PostgreSQL is faster when dealing with massive datasets, complicated queries, and read-write operations. On the other hand, MySQL is faster for read-only commands.

5. What is a Question Mark in PostgreSQL?

? is used as the placeholder for parameters in prepared statements. Since raw SQL queries aren't actually prepared, we can probably switch how we execute them.

6. How Do I Get Postgres Shell?

First, select the server on which PostgreSQL is running. By default, localhost is selected. If it’s running on a different machine, provide the server name here.

Next, select a database. During the Postgres installation, a database is created with the name postgres.

By default, the PostgreSQL server runs on port 5432, unless you change it during installation. If you changed the port number, provide the same here.

Provide the username. We will go with the default user “postgres.”

Type in the password for the user and click enter.

People are also Reading:

• Differences between MongoDB vs MySQL
• MariaDB vs MySQL
• Top PL/SQL Interview Questions and Answers
• SQL Interview Questions and Answers