PostgreSQL Tutorial

This PostgreSQL tutorial will teach you about PostgreSQL from beginner to advanced.

Section 1. Getting Started with PostgreSQL #

In this section, you’ll begin working with PostgreSQL by learning how to write SQL statements to interact with the databases.

What is PostgreSQL? Explain what PostgreSQL is and what you can do with it.
Creating a new table – Learn how to create a new table in PostgreSQL.
Inserting data into a table – Show you how to insert one or more rows into a table.
Selecting data from a table – Query data from a table using the SELECT statement.
Concatenating strings – Learn how to concatenate strings into a single string.
Dropping a table – Guide you to delete a table from a PostgreSQL database.

Section 2. Filtering Rows in a Table #

In this section, you’ll learn how to filter rows from a query to retrieve, update, and delete data from a table.

WHERE – Filter rows from a table using the WHERE clause.
BETWEEN – Check if a value is between two values.
IN – Determine if a value is in a list of values.
Update – Update data in one or more rows in a table.
Delete – Delete one or more rows from a table.

Section 3. Working with Tables #

In this section, you’ll learn how to design a database for an inventory management system from scratch. You’ll start by analyzing the business requirements, identifying tables and relationships, and creating tables with primary and foreign keys.

Database design – Start designing a database for the inventory management system from scratch.
Primary key – Show you how to define a primary key for a table.
NOT NULL constraint – Introduction to NULL and use the NOT NULL constraint to ensure a column does not have NULL.
DEFAULT constraint – Set default values for table columns.
CHECK constraint – Add validation rules to one or more columns to ensure values meet a condition.
UNIQUE constraint – Ensure values in a column or a set of columns are unique across rows within the same table.
Foreign key – Learn how to create foreign keys for a table.

Section 4. Ordering & Limiting Rows #

In this section, you’ll learn how to sort rows by values in one or more columns and limit the number of rows a query returns.

ORDER BY – Sort rows by one or more columns.
LIMIT – Return only a subset of rows from a query.
FETCH – Work like the LIMIT clause, which skips some rows and returns a subset of rows from a query.

Section 5. Joining Tables #

In this section, you’ll learn how to merge rows from two or more tables using various joining techniques, including inner join, left-right, right join, full join, cross join, and self-join.

Inner Join – Join two tables and select rows from the first table with matching rows in the right table.
Left Join – Join the left table with the right table and select all rows from the left table with the matching rows in the right table.
Right Join – Join the left table with the right table and select all rows from the right table with the matching rows in the left table.
Self-join – Join a table to itself to compare rows within the same table using an inner join, left join, or right join.
Full join – Merge rows from two tables and return matching and non-matching rows from both.
Cross Join – Join two tables and return a result set that include all possible combinations of rows from both tables.
Natural Join – Join two tables based on the column names.

Section 6. Grouping Rows #

In this section, you’ll learn how to divide rows of a query into groups and apply aggregate functions to each group.

GROUP BY – Group rows into groups by values in one or more columns of a table and apply an aggregate function to each group.
HAVING – Filter groups of rows based on a condition.

Section 7. Set operations #

In this section, you’ll learn how to perform common set operations including union, intersection, and except.

UNION – Append a result set to another and return a single result set.
INTERSECT – Return the intersection of two result sets.
EXCEPT – Find the difference between two result sets of two queries.

Section 8. PostgreSQL Data Types #

In this section, you will learn about different PostgreSQL data types used to define tables.

Boolean – Store Boolean data in the database, which includes the values true, false, and NULL.
Char – Store fixed-length and padded strings in the database.
Varchar – Store variable-length strings in the database.
Text – Store variable-length character data without a size limit.
Integer – Store integer numbers using various integer types.
Decimal – Store numeric data with precision using the decimal or numeric type.
Date – Store date without time data in the table.
Time – Store time data without date in the table.
Timestamp – Store local date and time without a time zone in the database.
Timestamp with a time zone – Learn how to handle timestamp with a time zone values using the TIMESTAMPTZ data type.
UUID – Show you how to use the UUID type for the primary key column.

Section 9. Subquery #

Subquery – Learn how to write a query nested within another query to form a more flexible queries
Correlated Subquery – Show you how to use a correlated subquery to select data that depends on the values of the outer query.
Subquery with IN operator – Show you to use a subquery with the IN operator to filter rows in the WHERE clause.
EXISTS – Learn how to use the EXISTS operator to check for existence of rows returned by a subquery.
ANY – Compare a value with a set of values returned by a subquery and return true if at least one comparison is true.
ALL – Compare a value with a set of values returned by a subquery and return true if all comparisons are true.

Section 10. Common Table Expression (CTE) #

In this section, you will learn how to use PostgreSQL common table expressions (CTE) to simplify complex queries and perform recursive queries.

CTE – Learn how to use a CTE to simplify complex queries.
Recursive CTE – Show you how to use recursive CTE to perform recursive queries.

Section 11. Selecting Distinct Rows #

In this section, you’ll learn how to select distinct rows from a table using the SELECT DISTINCT and SELECT DISTINCT ON clauses.

SELECT DISTINCT – Eliminate duplicate rows from a result set by values of one or more columns.
SELECT DISTINCT ON – Learn how to group rows into distinct groups and select the first row in each group.

Section 12. Database Views #

In this section, you’ll learn how to use database views to create reusable queries, simplify complex queries, and improve query performance.

View – Learn about views and how to use them to create reusable queries and simplify complex queries.
Materialized Views – Create materialized views to improve performance of expensive queries.

Section 13. Condition Expressions #

CASE expression – Add if-else logic to your queries to construct more flexible queries.
COALESCE – Return the first non-null argument, allowing you to replace NULL with a non-NULL value.
NULLIF – Return NULL if two arguments are the same, allowing you to replace a value with NULL.
GREATEST – Return the greatest value in a list of values.
LEAST – Return the smallest value in a list of values.

Section 14. Enhancing Table Structure #

ALTER TABLE – Modify a table structure by adding columns, removing columns, renaming table, …
Sequence – Learn how to use sequence to generate sequential integers.
Identity Column – Use identity column to automatically generate integer numbers based on an implicit sequence.
Adding columns to a table – Show you how to add one or more columns to a table.
Renaming tables – Learn how to change the name of a table to a new one.
Renaming columns – Guide you on how to change the name of an existing table column.
Dropping columns – Show you how to drop one or more columns from a table.

Section 15. Advanced Text Search #

LIKE – Find a string that match a pattern.
Regular Expressions – Learn how to use match operator (~) to search for strings that match POSIX regular expressions.
SIMILAR TO – Search for a string that matches a pattern containing wildcard characters (% and _ ) or regular expression.
Full-text search – Perform full-text searches.

Section 16. User-defined Functions #

Create functions – Learn how to create user-defined functions.
Drop functions – Show you how to remove one or more user-defined functions.

Section 17. Stored Procedures #

Create stored procedures – Show you how to create a stored procedure using the CREATE PROCEDURE statement.
Drop procedure – Learn how to remove a stored procedure from a database.

Section 18. Denormalized Data Types #

Composite types – Explain what composites are and how to manage the composite types such as creating new composite types.
Drop Type – Learn how to remove a user-defined type from the database.

Section 19. Triggers #

Triggers – Learn how to use triggers to automatically respond to an event that occurs on a table.
Drop Trigger – Guide you on how to drop a trigger from a table within your database.
Disabling Triggers – Show you how to turn off triggers of a table to avoid performance overhead when performing bulk data loading.
Event Triggers – Learn how to use event triggers to monitor and control database structure changes.
BEFORE INSERT Trigger – Use a before-insert trigger to automatically call a function before inserting a row into a table.
AFTER INSERT Trigger – Use an after-insert trigger to automatically call a function after an insert event occurs on a table.