Introduction

• General purpose and object-relational database management system, the most advanced open source database system
• PostgreSQL was designed to be portable so that it could run on various platforms such as Mac OS X, Solaris, and Windows
• Free and open source software
• Requires very minimum maintained efforts because of its stability

Installation

Download PostgreSQL

Install PostreSQL

• Installation Directory
• Select Components (Server, pgAdmin, CLI Tools)
• Data Directory
• Enter db superuser(postgres) password
• Port: 5432

Verify the Installation

• Open "SQL Shell (psql)"
• issue command: SELECT version();

Connect to PostgreSQL Server

psql

1. Launch program: "SQL Shell (psql)"
2. Enter necessary information
3. Interact with PostgreSQL DB by issuing an SQL statement

pgAdmin

1. Launch program: "pgAdmin4"
2. Right click the Servers node
3. Select create -> Server...
4. Enter Server Name
5. Click "Connection" tab
6. Enter "Host" and "password" for the postgres user
7. Click Save button

Expand the server

Open Query Tool

Enter the query

Connect to PostgreSQL db from other applications

PostgreSQL Sample Database

DVD rental database

• actor: stores actors data including first name and last name
• film: stores films data such as title, release year, length, rating, etc.
• film_actor: stores the relationships between films and actors.
• category: stores film's categories data
• film_category: stores the relationships between films and categories.
• store: contains the store data including manager staff and address.

DVD rental database (cont..)

• inventory: stores inventory data.
• rental: stores rental data.
• payment: stores cutomer's payments.
• staff: stores customer's payments.
• customer: stores customers data.
• address: stores address data for staff and customers.
• city: stores the city names.
• country: stores the country names.

Create a new Database

Create Database Statement

Load the sample db using psql tool

• Download 
• Launch Command Prompt (cmd)
• Navigate to bin folder of the PostgreSQL installation folder

• Use pg_restore to load sample db

Load the sample db using pgAdmin

• Download 
• Launch pgAdmin tool
• Right click on database and choose Restore... menu item
• Provide the path to sample db

• Click restore button

PostgreSQL Database Objects

Databases

Is a container of other objects such as tables, views, function, and indexes.

Tables

Used to store the data.
A special feature of PostgreSQL is table inheritence. A table (child table) can inherit from another table (parent table), so when you query the data from the child table, the data from the parent table is also showing up

Schemas

Is a logical container of tables and other objects inside a database. Schema is a part of the ANSI-SQL standard.

Tablespaces

Tablespace is a location on disk where PostgreSQL stores data files containing database objects e.g. indexes and tables.

Views

Virtual table that is used to simplify complex queries and to apply security for a set of records

Functions

Is a block reusable SQL code that returns a scalar value of a list record. 

Operators

Operators are symbolic functions. PostgreSQL allows you to define custom operators

Casts

Enable you to convert one data type into another data type

Sequence

Used to manage auto-increment columns that defined in a table as a serial column

Extension

Wrap other objects including types, casts, indexes, functions, etc. into a single unit.

PostgreSQL SELECT Statement

• Query data from tables using the SELECT statement
• One of the most complex statements in PostgreSQL
• It has many clauses that can be used to form a flexible query

SELECT statement clauses:

• DISTINCT: Select distinct rows
• ORDER BY: Sort rows
• WHERE: Filter rows
• LIMIT or FETCH: Select a subset of rows from a table
• GROUP BY: Group rows into groups
• HAVING: Filter groups
• Join with other tables:
  • INNER JOIN
  • LEFT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
• Perform ser operations:
  • UNION
  • INTERSECT
  • EXCEPT

SELECT statement syntax

PostgreSQL SELECT examples

Customer table

1. Use SELECT statement to query data from one column

2. Use SELECT statement to query data from multiple column

3. Use SELECT statement to query data in all columns

4. Use SELECT statement with expression

We used concatenation operator ||

5. Use SELECT statement with only expression

We skip the FROM clause because the statement does not refer to any table

PostgreSQL
ORDER BY

Sort rows returned from the SELECT statement in ascending or descending order based on specified criteria

ORDER BY clause syntax

PostgreSQL
ORDER BY examples

Customer table

1. Query sorts customers by the first name in ascending order:

2. Query sorts customers by the last name in descending order:

3. Query sorts customers by the first name in ascending order and then last name in descending order:

PostgreSQL
SELECT DISTINCT

DISTINCT clause is used in the SELECT statement to remove duplicate rows from a result set

DISTINCT clause syntax

If you specify multiple columns, the DISTINCT clause will evaluate the duplicate based on the combination of values of these columns

DISTINCT ON (expression)
to keep the "first" row of each group of duplicates:

PostgreSQL
SELECT DISTINCT examples

1. Create new table named t1 and insert data into the table

2. Insert some rows into the t1 table

3. Query the data from the t1 table using SELECT statement

DISTINCT on one column example

DISTINCT on multiple columns example

DISTINCT ON example

DISTINCT ON process:

PostgreSQL
WHERE clause

WHERE clause is to filter rows returned from the SELECT statement

WHERE clause syntax

• WHERE clause uses the condition to filter the rows returned from the SELECT statement
• The condition must evaluate to true, false, or unknown
• It can be Boolean expression or a combination of Boolean expression using AND and OR operators
• Only rows that cause the condition evaluates to true will be included in the result set

Standard comparison operators

PostgreSQL
WHERE clause examples

Customer table

1. WHERE clause with the equal (=) operator

2. WHERE clause with the AND operator

3. WHERE clause with the OR operator

4. WHERE clause with the IN operator

5. WHERE clause with the LIKE operator

The % is called a wildcard that matches any string. The 'Ann%' pattern matches any string that starts with 'Ann'

6. WHERE clause with the BEtWEEN operator

6. WHERE clause with the not equal  operator (<>)

PostgreSQL
LIMIT clause

LIMIT clause is an optional clause of the SELECT statement that returns a subset of rows returned by query

LIMIT clause syntax

The statement returns n rows generated by the query. If n is zero, the query returns an empty set.

OFFSET clause syntax

Skip rows before returning n rows

PostgreSQL
LIMIT clause examples

Film table

1. LIMIT clause to get the first 5 films ordered by film_id

2. Retrieve 4 films starting from the third one ordered by film_id

2. Get the top 10 most expensive films

PostgreSQL
IN operator

Use IN operator in the WHERE clause to check if a value matches any value in a list of values

IN operator syntax

The expression returns true if the value matches any value in the list

The statement inside the parentheses is called a subquery, which is a query nested inside another query

PostgreSQL
IN operator examples

1. Get rental information of customer id 1 and 2

2. Find all rentals with customer id is not 1 or 2

3. Returns a list of customer id of customers that has rental's return date on 2005-05-27

PostgreSQL
BETWEEN operator

Use BETWEEN operator to match a value against a range of values

BETWEEN operator syntax

If the value is greater than or equal to the low value and less than or equal to the high value, the expression returns true, otherwise, it returns false

If you want to check if a value is out of a range, you combine the NOT operator with the BETWEEN operator

PostgreSQL
BETWEEN operator examples

Payment table

1. Select the payment whose amount is between 8 ad 9

2. get payments whose amount is not in the range of 8 and 9

3. Check a value against of date ranges by using the literal date in ISO 8601 format

PostgreSQL
LIKE operator

Use LIKE operator for pattern matching technique

• Construct a pattern by combining a string with wildcard characters and use the LIKE or NOT LIKE operator to find the matches:
  • Percent (%): for matching any sequence of characters
  • Underscore (_) for matching any single character
• If the pattern does not contain any wildcard character, the LIKE operator acts like the equal (=) operator

LIKE operator syntax

The expression returns true if the string matches the pattern, otherwise it returns false

The expression returns true if LIKE returns true and vice versa

PostgreSQL
LIKE operator examples

1. Find cutomers whose first name contains er string

2. Find cutomers whose first name begins with any single character, followed by the literal string her, and ends with any number of characters

PostgreSQL
NOT LIKE operator examples

1. Find cutomers whose first name does not begin with Jen

PostgreSQL extension of LIKE operator

PostgreSQL provides the ILIKE operator that acts like the LIKE operator. ILIKE operator matches value
case-insensitively

1. The BAR% pattern matches any string that begins with BAR, Bar, BaR, etc.

PostgreSQL also provides some operators that act like the LIKE, NOT LIKE, ILIKE and NOT ILIKE operator as shown below:

• ~~ is equivalent to LIKE
• ~~* is equivalent to ILIKE
• !~~ is equivalent to NOT LIKE
• !~~* is equivalent to NOT ILIKE

PostgreSQL
IS NULL operator

NULL means missing or not applicable information

1. Create contacts table that stores the first name, last name, email and phone number of contact.

2. Define the phone column as nullable column and insert NULL into the phone column

3. Find the contact who does not have a phone number

The statement returns no row because the expression phone = NULL always return false. Even though there is NULL in the phone column, the expression NULL = NULL returns false. This is because NULL is not equal to any value even itself

IS NULL operator syntax

The expression returns true if the value is NULL or false if it is not

PostgreSQL
IS NULL operator examples

Get the contact who does not have any phone number stored in the phone column

PostgreSQL
IS NOT NULL operator examples

Get the contact who does  have phone number stored in the phone column

PostgreSQL
ALIAS operator

PostgreSQL alias assigns a table or a column a temporary name in a query. The aliases only exist during the execution of the query

Column ALIAS operator syntax

 the column_name is assigned an alias as alias_name. The AS keyword is optional so you can skip it

PostgreSQL
Column ALIAS operator examples

Find full names of all customers

• PostgreSQL evaluates the ORDER BY clause after the SELECT clause, you can use the column alias in the ORDER BY clause.
• For the other clauses evaluated before the SELECT clause such as WHERE, GROUP BY and HAVING, you cannot reference the column alias in these clauses

Table ALIAS operator syntax

PostgreSQL
JOINS

• PostgreSQL JOIN is used to combine columns from one (self-join) or more tables based on the values of the common columns between the tables.
• The common columns are typically the primary key columns of the first table and foreign key columns of the second table

PostgreSQL supports inner join, left join, right join, full outer join, cross join, natural join and a special kind of join called self-join

PostgreSQL
INNER JOIN clause

INNER JOIN clause is to get data from two tables named A and B. The B table has the fka field that relates to the primary key of the A table

INNER JOIN clause syntax

The expression returns true if the value is NULL or false if it is not

The INNER JOIN clause returns rows in A table that have the corresponding rows in the B table

PostgreSQL
INNER JOIN
clause examples
(2 tables)

Table customer and payment

Each customer may have zero or many payments. Each payment belongs to one and only one customer. The customer_id field establishes the link between two tables

Join customer table to payment

PostgreSQL
INNER JOIN
clause examples
(3 tables)

Table customer, staff and payment

• Each staff relates to zero or many payments. Each payment is processed by one and only one staff.
• Each customer has zero or many payments. Each payment belongs to one and only customer.

Join 3 tables

PostgreSQL
LEFT JOIN clause

• LEFT JOIN clause is used to select rows from the A table which may or may not have corresponding rows in the B table
• In case, there is no matching row in the B table, the values of the columns in the B table are substituted by the NULL values

LEFT JOIN clause syntax

• The LEFT JOIN clause returns all rows in the left table ( A) that are combined with rows in the right table ( B) even though there is no corresponding rows in the right table ( B).
• The LEFT JOIN is also referred as LEFT OUTER JOIN

PostgreSQL
LEFT JOIN
clause examples

Table film and inventory

Each row in the film table may have zero or many rows in the inventory table. Each row in the inventory table has one and only one row in the film table

Use LEFT JOIN clause to join film table to the inventory

Add a WHERE clause to select only films that are not in the inventory

PostgreSQL
SELF JOIN clause

• A self-join is a query in which a table is joined to itself. Self-joins are useful for comparing values in a column of rows within the same table
• To form a self-join, you specify the same table twice with different aliases, set up the comparison, and eliminate cases where a value would be equal to itself

SELF JOIN clause syntax

 table A is joined to itself using the INNER JOIN clause. you can also use the LEFT JOIN or RIGHT JOIN clause

PostgreSQL
SELF JOIN
clause examples

Table film

Finds all pair of films that have the same length

PostgreSQL
FULL OUTER JOIN clause

• The full outer join combines the results of both left join and right join.
• If the rows in the joined table do not match, the full outer join sets NULL values for every column of the table that lacks a matching row.
• For the matching rows , a single row is included in the result set that contains columns populated from both joined tables.

FULL OUTER JOIN clause syntax

The OUTER keyword is optional

PostgreSQL
SELF JOIN
clause examples

1. Create 2 new tables

Each department has zero or many employees and each employee belongs to zero or one department

2. Add some sample data into the departments and employees tables

3. Query data from the departments and employees tables

4. Use the FULL OUTER JOIN to query data from both employees and departments tables

5. result set includes every employee who belongs to a department and every department which have an employee. In addition, it includes every employee who does not belong to a department and every department that doesn’t have an employee

6. Find the department that does have any employee

6. Find the employee who does not belong to any department

PostgreSQL
CROSS JOIN clause

• CROSS JOIN clause allows you to produce the Cartesian Product of rows in two or more tables

• Different from the other JOIN operators such as LEFT JOIN  or INNER JOIN, the CROSS JOIN does not have any matching condition in the join clause

CROSS JOIN clause syntax

is also equivalent to:

Text

Use the INNER JOIN clause with the condition evaluates to true to perform the cross join

PostgreSQL
CROSS JOIN
clause examples

We will perform the CROSS JOIN of two tables T1 and T2. For every row from T1 and T2 i.e., a cartesian product, the result set will contain a row that consists of all columns in the T1 table followed by all columns in the T2 table. If T1 has N rows, T2 has M rows, the result set will have N x M rows

1. Create T1 and T2 tables and insert some sample data

2. use the CROSS JOIN operator to join the T1 table with the T2 table

3. Illustration Result of the CROSS JOIN operator when we join the T1 table with the T2

PostgreSQL
GROUP BY
clause

• GROUP BY clause divides the rows returned from the SELECT statement into groups.

• For each group, you can apply an aggregate function e.g.,  SUM() to calculate the sum of items or COUNT() to get the number of items in the groups

GROUP BY clause syntax

• The GROUP BY clause must appear right after the FROM or WHERE clause.
• Followed by the GROUP BY clause is one column or a list of comma-separated columns.
• Beside the table column, you can also use an expression with the GROUP BY clause

PostgreSQL
GROUP BY clause examples

Table payment

Gets data from the payment table and groups the result by customer id without applying an aggregate function

In this case, the GROUP BY acts like the DISTINCT clause that removes the duplicate rows from the result set

• Get how much a customer has been paid, by using the GROUP BY clause to divide the payments table into groups.
• For each group, you calculate the total amounts of money by using the SUM() function

Use the ORDER BY clause with GROUP BY clause to sort the groups

Count the number of transactions each staff has been processing and group the payments table based on staff id and use the COUNT() function to get the number of transactions 

PostgreSQL
HAVING clause

• HAVING clause often use in conjunction with the GROUP BY clause to filter group rows that do not satisfy a specified condition.

• HAVING clause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applies while the WHERE clause sets the condition for individual rows before GROUP BY clause applies.

• This is the main difference between the HAVING and WHERE clauses

HAVING clause syntax

PostgreSQL
HAVING clause with SUM function examples

Table payment

 Gets the total amount of each customer by using the GROUP BY clause and selects the only customer who has been spending more than 200

PostgreSQL
HAVING clause with COUNT examples

Table customer

Get number of customers per store

Use the HAVING clause to select store that has more than 300 customers

PostgreSQL
UNION operator

The UNION operator combines result sets of two or more SELECT statements into a single result set

UNION operator syntax

The following are rules applied to the queries:

• Both queries must return the same number of columns.
• The corresponding columns in the queries must have compatible data types.

• The UNION operator removes all duplicate rows unless the UNION ALL is used
• UNION operator often use to combine data from similar tables that are not perfectly normalized. Those tables are often found in the reporting or data warehouse system.

PostgreSQL
UNION operator examples

Let’s take a look at the following tables:

• sales2007q1: stores sales data in Q1 2007.
• sales2007q2: stores sales data in Q2 2007.

sales2007q1 data:

sales2007q2 data:

Use the UNION operator to combine data from both tables

There are five rows in the combined result set because the UNION operator removes one duplicate row

Use ORDER BY to sort the combined result returned by the UNION operator

JOIN combine columns

UNION combine rows

PostgreSQL
INTERSECT operator

• INTERSECT operator combines the result sets of two or more SELECT statements into a single result set.
• The INTERSECT operator returns any rows that are available in both result set or returned by both queries.

INTERSECT operator syntax

To use the INTERSECT operator, the columns that appear in the SELECT statements must follow the rules below:

• The number of columns and their order in the SELECT clauses must the be the same.
• The data types of the columns must be compatible.

PostgreSQL
INTERSECT operator examples

 1. Creates 3 tables: employees , keys, and  hipos

The employees table stores the employee master data. The keys table stores the key employees and the  hipos table stores the employees who are high potential and high impact

 2.  Insert some sample data into the employees, keys, and  hipos tables

 3.  Get key employees from the keys table

4. Get list of employee_id from the  hipos table

5. Get the employees who are both keys, and high potential and high impact

6. place the ORDER BY clause at the end of the statement, not at the end of each SELECT statement to sort the result set returned by the INTERSECT operator

INTERSECT

• Intersect does all columns.

• When using INTERSECT operator the number and the order of the columns must be the same in all queries as well data type must be compatible.

• Use an intersect operator to returns rows that are in common between two tables; it returns unique rows from both the left and right query.

• This query is useful when you want to find results that are in common between two queries.

INNER JOIN

• Inner join only the specified columns.

• An INNER JOIN is a JOIN operation that allows you to specify an explicit join clause.

• An inner join attempts to match up the two tables based on the criteria you specify in the query, and only returns the rows that match.

• If a row from the first table in the join matches two rows in the second table, then two rows will be returned in the results

INTERSECT operator returns almost same results as INNER JOIN clause many times

PostgreSQL
EXCEPT operator

• EXCEPT operator returns rows by comparing the result sets of two or more quires
• EXCEPT operator returns distinct rows from the first (left) query that are not in the output of the second (right) query

EXCEPT operator syntax

To combine the queries using the EXCEPT operator, you must obey the following rules:

• The number of columns and their orders must be the same in the two queries.
• The data types of the respective columns must be compatible.

PostgreSQL
EXCEPT operator examples

1. Get films in the film table

2. Get films that are in the inventory

Both queries return a result set that consists of two columns: film_id and title

3. Use EXCEPT operator to get the films that are not in the inventory

• LEFT JOIN and EXCEPT do not produce the same results
• EXCEPT is set operator that eliminates duplicates. LEFT JOIN is a type of join, that can actually produce duplicates

LEFT JOIN vs EXCEPT

PostgreSQL
GROUPING SETS

A grouping set is a set of columns by which you group. Typically, a single aggregate query defines a single grouping set

PostgreSQL
GROUPING SETS examples

1. Create a new sales table

The sales table stores the number of products sold by brand and segment

2. Get number of products sold by brand and segment

3. Finds the number of product sold by brand with a grouping set of the brand

4. Finds the number of products sold by segment with a grouping set of the segment

5. Finds the number of products sold for all brands and segments with an empty grouping set

Suppose that instead of four result sets, you wanted to see a unified result set with the aggregated data for all grouping sets. To achieve this, you use the UNION ALL to unify all the queries above

6. UNION ALL requires all result sets to have the same number of columns with compatible data types, you need to adjust the queries by adding NULL to the selection list of each

• Previous query generated a single result set with the aggregates for all grouping sets
•  It has two main problems:
  • ​It is quite lengthy
  • It has a performance issue because PostgreSQL has to scan the sales table separately for each query
• To make it more efficient, PostgreSQL provides the GROUPING SETS which is the subclause of the GROUP BY clause

GROUPING SETS operator syntax

• GROUPING SETS allows you to define multiple grouping sets in the same query
• In this syntax, you have four grouping sets (c1,c2), (c1), (c2), and ()

7. Use GROUPING SETS instead of UNION ALL to the example above

Grouping function

GROUPING function accepts a name of a column and returns bit 0 if the column is the member of the current grouping set and 1 otherwise

Example:

PostgreSQL
CUBE

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

CUBE subclause syntax

Specify the columns (dimensions or dimension columns) which you want to analyze and aggregation function expressions

• The query generates all possible grouping sets based on the dimension columns specified in CUBE.
• The CUBE subclause is a short way to define multiple grouping sets so the following are equivalent:

In general, if the number of columns specified in the CUBE is n, then you will have 2^n  combinations

PostgreSQL allows you to perform a partial cube to reduce the number of aggregates calculated. The following shows the syntax:

PostgreSQL
CUBE examples

1. Use the sales table created in the GROUPING SETS (previously)

2. Use CUBE subclause to generate multiple grouping sets

Query to performs a partial cube

PostgreSQL
ROLLUP

• ROLLUP is a subclause of the GROUP BY clause that offers a shorthand for defining multiple grouping sets. A grouping set is a set of columns to which you want to group

• Different from the CUBE subclause, ROLLUP does not generate all possible grouping sets based on the specified columns. It just makes a subset of those.

• The ROLLUP assumes a hierarchy among the input columns and generates all grouping sets that make sense considering the hierarchy. This is the reason why ROLLUP is often used to generate the subtotals and the grand total for reports

For example, the CUBE (c1,c2,c3) makes all eight possible grouping sets:

However, the ROLLUP(c1,c2,c3) generates only four grouping sets, assuming the hierarchy c1 > c2 > c3 as follows:

A common use of  ROLLUP is to calculate the aggregations of data by year, month, and date, considering the hierarchy
year > month > date

ROLLUP syntax

Specify the columns (dimensions or dimension columns) which you want to analyze and aggregation function expressions

It is also possible to do a partial roll up to reduce the number of subtotals generated

PostgreSQL
ROLLUP examples

1. Use the ROLLUP subclause to find the number of products sold by brand (subtotal) and by all brands and segments (total)

• As you can see from the output, the third row shows the number of products sold for the ABC brand, the sixth row displays the number of products show for the XYZ brand.
• The last row shows the grand total which displays the total products sold for all brands and segments. In this example, the hierarchy is
  brand > segment.

Perform a partial rollup

Other Example

Table rental

Finds the number of rental per day, month, and year by using the ROLLUP

PostgreSQL
SUBQUERY

SUBQUERY allows us to construct complex query

Suppose we want to find the films whose rental rate is higher than the average rental rate. We can do it in two steps:

• Find the average rental rate by using the SELECT statement and average function ( AVG).
• Use the result of the first query in the second SELECT statement to find the films that we want.

SIMPLE EXAMPLE

1. Gets the average rental rate

The average rental rate is 2.98

2. Get films whose rental rate is higher than the average rental rate

• The code is not so elegant, which requires two steps. We want a way to pass the result of the first query to the second query in one query. The solution is to use a subquery.
• A subquery is a query nested inside another query such as SELECT, INSERT, DELETE and UPDATE. In this training, we are focusing on the SELECT statement only.

SUBQUERY EXAMPLE

To construct a subquery, we put the second query in brackets and use it in the WHERE clause as an expression

The query inside the brackets is called a subquery or an inner query. The query that contains the subquery is known as an outer query

PostgreSQL executes the query that contains a subquery in the following sequence:

• First, executes the subquery.
• Second, gets the result and passes it to the outer query.
• Third, executes the outer query.

PostgreSQL SUBQUERY with IN operator

• A subquery can return zero or more rows. To use this subquery, use the IN operator in the WHERE clause
• Get films that have the returned date between 2005-05-29 and 2005-05-30

It returns multiple rows so we can use this query as a subquery in the WHERE clause of a query

PostgreSQL SUBQUERY with EXIST operator

• A subquery can be an input of the EXISTS operator. If the subquery returns any row, the EXISTS operator returns true. If the subquery returns no row, the result of EXISTS operator is false

• The EXISTS operator only cares about the number of rows returned from the subquery, not the content of the rows, therefore, the common coding convention of EXISTS operator is as follows:

The query works like an inner join on the customer_id column. However, it returns at most one row for each row in the customer table even though there are some corresponding rows in the payment table

PostgreSQL
ANY Operator

ANY operator compares a value to a set of values returned by a subquery

ANY operator syntax

• The subquery must return exactly one column.
• The ANY operator must be preceded by one of the following comparison operator =, <=, >, <, > and <>
• The ANY operator returns true if any value of the subquery meets the condition, otherwise, it returns false.

PostgreSQL
ANY Operator examples

Table film and film_category

1. Get the maximum length of film grouped by film category

2. Use the previous query as a subquery in the following statement that finds the films whose lengths are greater than or equal to the maximum length of any film category

ANY vs IN

The = ANY is equivalent to IN operator

Gets the film whose category is either Action or Drama

The following statement uses the IN operator which produces the same result:

• Note that the <> ANY operator is different from NOT IN.

• The following expression: 

is equivalent to

PostgreSQL
ALL Operator

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

ALL operator syntax

• The ALL operator must be preceded by a comparison operator such as equal (=), not equal (!=), greater than (>), greater than or equal to (>=), less than (<), and less than or equal to (<=).
• The ALL operator must be followed by a subquery which also must be surrounded by the parentheses

With the assumption that the subquery returns some rows, the ALL operator works as follows:

1. column_name > ALL (subquery) the expression evaluates to true if a value is greater than the biggest value returned by the subquery.
2. column_name >= ALL (subquery) the expression evaluates to true if a value is greater than or equal to the biggest value returned by the subquery.
3. column_name < ALL (subquery) the expression evaluates to true if a value is less than the smallest value returned by the subquery.
4. column_name <= ALL (subquery) the expression evaluates to true if a value is less than or equal to the smallest value returned by the subquery.
5. column_name = ALL (subquery) the expression evaluates to true if a value is equal to any value returned by the subquery.
6. column_name != ALL (subquery) the expression evaluates to true if a value is not equal to any value returned by the subquery.

PostgreSQL
ALL operator examples

Table film

1. Get the average lengths of all films grouped by film rating

2. Find all films whose lengths are greater than the list of the average lengths above by using  the ALL and greater than operator (>)

PostgreSQL
EXIST Operator

The EXISTS operator is used to test for existence of rows in a subquery

EXIST operator syntax

• The EXISTS accepts an argument which is a subquery.

• If the subquery returns at least one row, the result of EXISTS is true. In case the subquery returns no row, the result is of EXISTS is false.

• EXISTS is often used with the correlated subquery.

• The result of EXISTS depends on whether any row returned by the subquery, and not on the content of the rows. Therefore, the columns that appear on the SELECT clause of the subquery are not important.

The common coding convention is to write EXISTS in the following form:

Note that if the subquery returns NULL, the result of EXISTS is true.

PostgreSQL
EXIST operator examples

Table customer and payment

1. Find customers who have at least one payment whose amount is greater than 11

In this example, for each customer in the customer table, the subquery checks the payment table to find if that customer made at least one payment (p.customer_id = c.customer_id) and the amount is greater than 11 ( amount > 11)

2. NOT EXISTS example

NOT EXISTS is opposite to EXISTS, meaning that if the subquery returns no row, NOT EXISTS returns true. If the subquery returns any rows, NOT EXISTS returns false

Find customers who have not made any payment that greater than 11

3. EXIST and NULL

The subquery returned NULL, therefore, the query returned all rows from the customer table

If the subquery returns NULL, EXISTS returns true

PostgreSQL
INSERT statement

INSERT statement that allows you to insert one or more rows into a table at a time

INSERT statement syntax

To add multiple rows into a table at a time, use the following syntax:

INSERT statement syntax (Cont.)

To insert data that comes from another table, use the INSERT INTO SELECT statement as follows:

PostgreSQL
INSERT statement examples

Create new table

Insert 1 row

• To insert character data, you must enclose it in single quotes (‘) for example 'PostgreSQL Training'. For the numeric data type, you don’t need to do so, just use plain numbers such as 1, 2, 3.

• If you omit any column that accepts the NULL value in the INSERT statement, the column will take its default value. In case the default value is not set for the column, the column will take the NULL value.

• PostgreSQL provides a value for the serial column automatically so you do not and should not insert a value into the serial column.

If you want to insert a string that contains a single quote character such as O'Reilly Media, you have to use a single quote (‘) escape character as shown in the following query:

Insert multiple rows

Insert date

add a new column named last_update into the link table and set its default value to CURRENT_DATE

inserts a new row with specified date into the link table. The date format is YYYY-MM-DD

Insert date (cont..)

You can also use the DEFAULT keyword to set the default value for the date column or any column that has a default value.

Insert data from another table

1. Create new table

2. Insert rows from the link table whose values of the date column are not NULL

Get the last insert id

Get the last insert id from the table after inserting a new row, use the RETURNING clause in the INSERT statement

PostgreSQL
UPDATE statement

UPDATE statement to update existing data in a table

UPDATE statement syntax

Determine which rows you want to update in the condition of the WHERE clause. If you omit the WHERE clause, all the rows in the table are updated

PostgreSQL
UPDATE statement examples

• Use the link table created in the INSERT chapter

• Update table partially example

PostgreSQL update table partially example

PostgreSQL update all rows in a table

PostgreSQL update join example

updates values that come from the link table for the columns in the link_tmp table

This kind of UPDATE statement sometimes referred to as UPDATE JOIN or UPDATE INNER JOIN because two or more tables are involved in the UPDATE statement. The join condition is specified in the WHERE clause

PostgreSQL update update with returning clause

 Updates the row with id 1 in the link table and returns the updated entries

PostgreSQL
UPDATE JOIN statement

UPDATE JOIN statement allows you to update data of a table based on values in another table

UPDATE JOIN statement syntax

• To join to another table in the UPDATE statement, you specify the joined table in the FROM clause and provide the join condition in the WHERE clause. The FROM clause must appear immediately after the SET clause.

• This form of the UPDATE statement updates column value c1 in the table A if each row in the table A and B have a matching value in the column c2

PostgreSQL
UPDATE JOIN statement examples

Create 2 new table

• Create a new table called product_segment that stores the product segments such as grand luxury, luxury and mass.
• The product_segment table has the discount column that stores the discount percentage based on a specific segment.

• Create another table named product that stores the product data
• The product table has the foreign key column segment_id that links to the id of the segment table

Calculate the net price of every product based on the discount of the product segment

• This statement joins the product table to the product_segment table.
• If there is a match in both tables, it gets the discount from the product_segment table, calculates the net price based on the following formula, and updates the net_price column

Check the data of the product table

PostgreSQL Boolean Data Type

• PostgreSQL supports a single Boolean data type: BOOLEAN that can have three states: TRUE, FALSE, and NULL.
• PostgreSQL uses one byte for storing a boolean value in the database.
• The BOOLEAN can be abbreviated as BOOL

The following table shows the valid literal values for TRUE and FALSE in PostgreSQL

Note that the leading or trailing whitespace does not matter and all the constant values except for true and false must be enclosed in single quotes

PostgreSQL
BOOLEAN
examples

 1. Create a new table stock_availability to log which product are available

 2. insert some sample data into the stock_availability table. Use various literal value for the boolean values

3. Check the products that are available

PostgreSQL Character Types

PostgreSQL provides three primary character types: character(n) or char(n), character varying(n) or varchar(n), and text, where n is a positive integer.

• Both char(n) and varchar(n) can store up to n characters in length. If you try to store a longer string in the column that is either char(n) or varchar(n), PostgreSQL will issue an error
• One exception is that if the excess characters are all spaces, PostgreSQL will truncate the spaces to the maximum length and store the string.
• The text data type can store a string with unlimited length
• Different from other database systems, in PostgreSQL, there is no performance difference among three character types. In most situation, you should use text or varchar, and varchar(n) if you want PostgreSQL to check for the length limit

PostgreSQL
Character Type
examples

1. Create a new table

2. Insert a new row into the character_tests table

2. Resolve error

PostgreSQL Numeric Types

The NUMERIC type can store numbers with many digits. Typically, you use the NUMERIC type for the monetary or other amounts which precision are required

NUMERIC data type syntax

• The precision is the total number of digits, while the scale is the number of digits in the fraction part. For example, the number 1234.567 has a precision of seven and a scale of three.

• The NUMERIC value can have up to 131,072 digits before the decimal point 16,383 digits after the decimal point.

The scale of the NUMERIC type can be zero or positive, so the following syntax defines a NUMERIC column with the scale of zero:

If you omit both precision and scale, you can store any precision and scale up to the limit of the precision and scale mentioned in previous slide.

In PostgreSQL, the NUMERIC and DECIMAL types are equivalent and both of them are also a part of SQL standard.

PostgreSQL
Numeric Data Type
examples

 1. Create a new table named products

2. Insert some products with the prices whose scales exceed the scale declared in the price column

Because the scale of the price column is two, PostgreSQL rounds the value 500.215 up to 500.22 and rounds the value 500.214 down to 500.21

PostgreSQL
Numeric and NaN
examples

NUMERIC column also can store a special value called not-a-number or NaN

Note that you must use quotes to wrap the theNaN

• Typically, the NaN is not equal to any number including itself. It means that the expression NaN = NaN returns false.
• However, PostgreSQL treats NaN values are equal and NaN is greater than any non-NaN value. This implementation allows PostgreSQL to sort NUMERIC values and use them in the tree-based indexes

PostgreSQL Integer Data Types

To store the whole numbers in PostgreSQL, you use one of the following integer types: SMALLINT, INTEGER, and BIGINT

The specification of each integer type

If you try to store a value outside of the permitted range, PostgreSQL will issue an error

SMALLINT

• The SMALLINT requires 2 bytes storage size which can store any integer numbers that is in the range of (-32,767, 32,767)
• You can use the SMALLINT type for storing something like ages of people, the number of pages of a book

The pages column is a SMALLINT column. Because the number of pages of a book must be positive, we added a CHECK constraint to enforce this rule

INTEGER

• The most common choice between integer types because it offers the best balance between storage size, range, and performance
• The INTEGER type requires 4 bytes storage size that can store numbers in the range of (-2,147,483,648, 2,147,483,647)
• You can use the INTEGER type for a column that stores quite big whole numbers like the population of a city or even country

Notice that INT is the synonym of INTEGER

BIGINT

• In case you want to store the whole numbers that are out of the range of the INTEGER type, you can use the BIGINT type.
• The BIGINT type requires 8 bytes storage size that can store any number in the range of (-9,223,372,036,854,775,808,+9,223,372,036,854,775,807).
• Using BIGINT type is not only consuming a lot of storage but also decreasing the performance of the database, therefore, you should have a good reason to use it

PostgreSQL Date Data Types

• PostgreSQL uses 4 bytes to store a date value
• When storing a date value, PostgreSQL uses the  yyyy-mm-dd format e.g., 2000-12-31. It also uses this format for inserting data into a date column
• If you create a table that has a DATE column and you want to use the current date as the default value for the column, you can use the CURRENT_DATE after the DEFAULT keyword

Following statement creates the documents table that has the posting_date column with the DATE data type. The posting_date column accepts the current date as the default value

PostgreSQL DATE functions

 Create a new employees table

1. Get the current date

To get the current date and time, you use the built-in NOW() function. However, to get the date part only (without the time part), you use the double colons (::) to cast a DATETIME value to a DATE value

The result is in the format:  yyyy-mm-dd. However, you can output a date value to various formats

2. Output a PostgreSQL date value in a specific format

The TO_CHAR() function accepts two parameters. The first parameter is the value that you want to format, and the second one is the template that defines the output format

The result is in the format:  yyyy-mm-dd. However, you can output a date value to various formats

3. Get the interval between two dates

• Use the minus (-) operator
• Get the days of service of the employees, you use subtract the values in the hire_date column from the date of today

4. Calculate ages in years, months, and days

• Use the AGE() function
• Calculate the ages of employees in the employees table

4.1. Calculate ages in years, months, and days (Cont..)

If you pass a date value to the AGE() function, it will subtract that date value from the current date. If you pass two arguments to the AGE() function, it will subtract the second argument from the first argument

5. Extract year, quarter, month, week, day from a date value

use EXTRACT() function to extracts the birth dates of employee into year, month, and day

PostgreSQL Timestamp Data Types

• Timestamp: Data type allows you to store both date and time. However, it does not have any time zone data. It means that when you change your database server’s time zone, the timestamp value stored in the database does not change
• Timestamptz: Data is the timestamp with time zone. The timestamptz is a time zone-aware date and time data type. PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table

• When you query timestamptz from the database, PostgreSQL converts the UTC value back to the time value of the timezone set by the database server, the user, or the current database connection
• Both timestamp and timestamptz uses 8 bytes for storing the timestamp value

PostgreSQL TIMESTAMP Example

Create a table that consists of both timestamp the timestamptz columns

Set the time zone of the database server to  Asia/Kuala_Lumpur

Viewthe current time zone using the SHOW TIMEZONE command

Insert a new row into the timstamp_demo table

Viewthe current time zone using the SHOW TIMEZONE command

Insert a new row into the timstamp_demo table

Change the timezone of the current session to America/New_York and query data again

The value in the timestamp column does not change, whereas the value in the timestamptz column is adjusted to the new time zone of 'America/New_York'

PostgreSQL TIMESTAMP
functions

1. Getting the current time

Use the NOW() function

Note that both CURRENT_TIMESTAMP and CURRENT_TIME return the current time with time zone

Get the time of day in the string format, you use the timeofday() function

2. Convert between timezones

Convert a timestamp to another time zone by using the timezone(zone, timestamp) function

Note that we pass the timestamp as a string to the timezone() function, PostgreSQL casts it to timestamptz implicitly. It is better to cast a timestamp value to the timestamptz data type explicitly as the following statement

PostgreSQL Time Data Types

PostgreSQL provides the TIME data type that allows you to store the time of day values

How to declare TIME

A time value may have a precision up to 6 digits. The precision specifies the number of fractional digits placed in the second field

• The TIME data type requires 8 bytes and its allowed range is from 00:00:00 to 24:00:00
• Common format of TIME values

PostgreSQL
TIME examples

 1. Create a new table named shifts

2. insert some rows into the shifts table

3. Query data from the shifts table

PostgreSQL TIME with time zone type

Besides the TIME data type, PostgreSQL provides the TIME with time zone data type that allows you to store and manipulate the time of day with time zone

How to declare TIME with time zone

The storage size of the TIME with time zone data type is 12 bytes that allow you store a time value with the time zone that ranges from 00:00:00+1459 to 24:00:00-1459

Handling PostgreSQL TIME values

1. Getting the current time

Use the CURRENT_TIME function to get current time with time zone

Use the CURRENT_TIME(precision) function to get the current time with a specific precision

Notice that without specifying the precision, the CURRENT_TIME function returns a time value with the full available precision

1. Getting the current time (Cont..)

Use the LOCALTIME function to get the local time

To get the local time with a specific precision, you use the LOCALTIME(precision) function

2. Converting time to a different time zone

To convert time to a different time zone, you use the following form

3. Extracting hours, minutes, seconds from a time value

To extracting hours, minutes, seconds from a time value, you use the EXTRACT function

4. Arithmetic operations on time values

PostgreSQL allows you to apply arithmetic operators such as +, -,  and *  on time values and between time and interval values

PostgreSQL JSON Data Types

• JSON stands for JavaScript Object Notation.
• JSON is an open standard format that consists of key-value pairs.
• The main usage of JSON is to transport data between a server and web application. Unlike other formats, JSON is human-readable text

 1. Create a new table

2. Insert data into a JSON column

3. Insert more data

Querying JSON data

1. Use the SELECT statement

• PostgreSQL returns a result set in the form of JSON.
• PostgreSQL provides two native operators -> and ->> to help you query JSON data.
  • The operator -> returns JSON object field by key.
  • The operator ->> returns JSON object field by text.

2. Use the operator -> to get all customers in form of JSON

3. Use operator ->> to get all customers in form of text

4. Because -> operator returns a JSON object, you can chain it with the operator ->> to retrieve a specific node

First  info -> 'items' returns items as JSON objects. And then info->'items'->>'product' returns all products as text

Use JSON operator in WHERE clause

find out who bought Diaper

1. Find out who bought Diaper

find out who bought Diaper

2. Find out who bought two products at a time

Notice that we used the type cast to convert the qty field into INTEGER type and compare it with two

Apply aggregate functions to JSON data

We can apply aggregate functions such as MIN, MAX, AVERAGE, SUM, etc., to JSON data

PostgreSQL JSON functions

1. json_each function

json_each() function allows us to expand the outermost JSON object into a set of key-value pairs

If you want to get a set of key-value pairs as text, you use the json_each_text() function instead

2. json_object_keys 

Use json_object_keys() function to get a set of keys in the outermost JSON object

3. json_typeof function

json_typeof() function returns type of the outermost JSON value as a string. It can be number, boolean, null, object, array, and string

3. json_typeof function (Cont...)

The following query returns the data type of the qty field of the nested items JSON object

Reference

Cheatsheet

PostgreSQL CONDITIONAL TABLE EXPRESSION (CTE)

• A common table expression is a temporary result set which you can reference within another SQL statement including SELECT, INSERT, UPDATE or DELETE

• Common Table Expressions are temporary in the sense that they only exist during the execution of the query

 Syntax of creating a CTE

• First, specify the name of the CTE following by an optional column list.
• Second, inside the body of the WITH clause, specify a query that returns a result set. If you do not explicitly specify the column list after the CTE name, the select list of the CTE_query_definition will become the column list of the CTE.
• Third, use the CTE like a table or view in the statement which can be a SELECT, INSERT, UPDATE, or DELETE.

CTE Example

PostgreSQL CASE Conditional

CASE expression is the same as IF/ELSE statement in other programming languages

 General form of the CASE statement

• Each condition is an expression that returns a boolean value, either true or false
• All result expressions must have data types that can be convertible to a single data type e.g., string, numeric, and temporal

PostgreSQL CASE Example

Film table

PostgreSQL
NULLIF 

NULLIF function is one of the most common conditional expressions provided by PostgreSQL

PostgreSQL NULLIF function syntax

NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1

Example:

PostgreSQL NULLIF function example

1.  Create a new table named members

2. Insert some rows

3. Calculate the ratio between male and female members

4. Remove the female member

Execute the query to calculate the male/female ratio again, we got the following error message:

This is because, the number of female is zero. To prevent this division by zero error, we use the NULLIF function

PostgreSQL CAST Operator

Convert a value of one data type into another

PostgreSQL CAST operator syntax

• First, specify an expression that can be a constant, a table column, an expression that evaluates to a value.
• Then, specify the target data type to which you want to convert the result of the expression.

Alternative of CAST operator syntax

Example:

PostgreSQL CAST
examples

1. Cast a string to an integer example

If the expression cannot be converted to the target type, PostgreSQL will raise an error

2. Cast a string to a date example

If the expression cannot be converted to the target type, PostgreSQL will raise an error

3. Cast a string to a double example

You need to use DOUBLE PRECISION instead of DOUBLE

4. Cast a string to a boolean example

5. Convert a string to a timestamp example

6. Using CAST with table data example

Create a ratings table

 Insert some sample data into the ratings table

6. Using CAST with table data example (cont..)

Because the requirements change, we use the same ratings table to store ratings as number e.g., 1, 2, 3 instead of A, B, and C

6. Using CAST with table data example (cont..)

So the ratings table stores mixed values including numeric and string

6. Using CAST with table data example (cont..)

Convert all values in the rating column into integers, all other A, B, C ratings will be displayed as zero