PostgreSQL Tutorial

This PostgreSQL tutorial helps you understand PostgreSQL quickly. You’ll master PostgreSQL very fast through many practical examples and apply the knowledge in developing applications using PostgreSQL.

If you are…

• Looking for learning PostgreSQL fast.
• Developing applications using PostgreSQL as the back-end database management system.
• Migrating from other database management systems such as MySQL, Oracle, and Microsoft SQL Server to PostgreSQL.

You’ll find all you need to know to get started with PostgreSQL quickly and effectively here on this website.

PostgreSQL tutorial demonstrates many unique features of PostgreSQL that make it the most advanced open-source database management system.

Getting Started with PostgreSQL

This section helps you get started with PostgreSQL by showing you how to install PostgreSQL on Windows, Linux, and macOS. You also learn how to connect to PostgreSQL using the psql tool as well as how to load a sample database into the PostgreSQL for practicing.

Basic PostgreSQL Tutorial

First, you’ll learn how to query data from a single table using basic data querying techniques, including selecting data, sorting result sets, and filtering rows. Then, you’ll learn about advanced queries such as joining multiple tables, using set operations, and constructing the subquery. Finally, you will learn how to manage database tables, such as creating a new table or modifying an existing table’s structure.

Section 1. Querying Data

• Select – show you how to query data from a single table.
• Column aliases – learn how to assign temporary names to columns or expressions in a query.
• Order By – guide you on how to sort the result set returned from a query.
• Select Distinct – provide you with a clause that removes duplicate rows in the result set.
• Paginated Select – guide you on how to paginate the result set returned from a query.

Section 2. Filtering Data

• Where – filter rows based on a specified condition.
• Limit – get a subset of rows generated by a query.
• Fetch – limit the number of rows returned by a query.
• In – select data that matches any value in a list of values.
• Between – select data that is a range of values.
• Like – filter data based on pattern matching.
• Is Null – check if a value is null or not.

Section 3. Joining Multiple Tables

• Joins – show you a brief overview of joins in PostgreSQL.
• Table aliases – describes how to use table aliases in the query.
• Inner Join – select rows from one table that has the corresponding rows in other tables.
• Left Join – select rows from one table that may or may not have the corresponding rows in other tables.
• Self-join – join a table to itself by comparing a table to itself.
• Full Outer Join – use the full join to find a row in a table that does not have a matching row in another table.
• Cross Join – produce a Cartesian product of the rows in two or more tables.
• Natural Join – join two or more tables using implicit join conditions based on the common column names in the joined tables.
• Lateral Join – Cross-reference rows in a subquery by joining, and build a composite result set.

Section 4. Grouping Data

• Group By – divide rows into groups and applies an aggregate function on each.
• Having – apply conditions to groups.
• Partition By – divide rows into groups and applies an window function on each.

Section 5. Set Operations

• Union – combine result sets of multiple queries into a single result set.
• Intersect – combine the result sets of two or more queries and returns a single result set that has the rows appear in both result sets.
• Except – return the rows in the first query that does not appear in the output of the second query.

Section 6. Grouping sets, Cube, and Rollup

• Grouping Sets – generate multiple grouping sets in reporting.
• Cube – define multiple grouping sets that include all possible combinations of dimensions.
• Rollup – generate reports that contain totals and subtotals.

Section 7. Subquery

• Subquery – write a query nested inside another query.
• Correlated Subquery – show you how to use a correlated subquery to perform a query that depends on the values of the current row being processed.
• ANY – retrieve data by comparing a value with a set of values returned by a subquery.
• ALL – query data by comparing a value with a list of values returned by a subquery.
• EXISTS – check for the existence of rows returned by a subquery.

Section 8. Common Table Expressions

• PostgreSQL CTE – introduce you to PostgreSQL common table expressions or CTEs.
• Recursive query using CTEs – discuss the recursive query and learn how to apply it in various contexts.

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, and remove data with the DELETE statement. Besides, you learn how to use the upsert statement to merge data.

• Insert – guide you on how to insert a single row into a table.
• Insert multiple rows – show you how to insert multiple rows into a table.
• Update – update existing data in a table.
• Update join – update values in a table based on values in another table.
• Delete – delete data in a table.
• Delete join – delete rows in a table based on values in another table.
• Delete cascade – delete related rows in child tables when a parent row is deleted from the parent table.
• Upsert – insert or update data if the new row already exists in the table.
• Merge – conditionally insert, update, and delete rows of a table.

Section 10. Transactions

• PostgreSQL Transactions – show you how to handle transactions in PostgreSQL using BEGIN, COMMIT, and ROLLBACK statements.
• PostgreSQL Subtransactions – show you how to handle subtransactions in PostgreSQL using SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT statements.
• Concurrent locks and MultiXacts – illustrate what a MultiXact is, why it exists, and under what circumstances one comes into existence.
• SKIP LOCKED – show you how to use SKIP LOCKED for avoiding and resolving deadlocks in PostgreSQL.

Section 11. Import & Export Data

You will learn how to use the copy command and DBeaver tool, to import and export PostgreSQL data in CSV and SQL file formats.

• Import CSV file into Table – show you how to import CSV file into a table.
• Export Table to CSV file – show you how to export tables to a CSV file.
• Import Data using DBeaver – show you how to import data from files to tables using DBeaver.
• Export Table using DBeaver – show you how to use DBeaver to export tables to a file in different types and formats.
• Merge Data using DBeaver – show you how to merge data from files to tables using DBeaver.

Section 12. Managing Tables

In this section, you will start exploring the PostgreSQL data types and show you how to create new tables and modify the structure of the existing tables.

• Data types – cover the most commonly used PostgreSQL data types.
• Create a table – guide you on how to create a new table in the database.
• Select Into & Create table as – shows you how to create a new table from the result set of a query.
• Auto-increment column with SERIAL – uses SERIAL to add an auto-increment column to a table.
• Sequences – introduce you to sequences and describe how to use a sequence to generate a sequence of numbers.
• Identity column – show you how to use the identity column.
• Generated columns – show you how to use the generated column.
• Alter table – modify the structure of an existing table.
• Rename table – change the name of the table to a new one.
• Add column – show you how to use add one or more columns to an existing table.
• Drop column – demonstrate how to drop a column of a table.
• Change column data type – show you how to change the data of a column.
• Rename column – illustrate how to rename one or more columns of a table.
• Drop table – remove an existing table and all of its dependent objects.
• Truncate table – remove all data in a large table quickly and efficiently.
• Temporary table – show you how to use the temporary table.
• Copy a table – show you how to copy a table to a new one.
• Table partitioning – show you how to use table partitioning.
• Managing partitioned tables – show you how to manage partitioned tables.
• DDL commands that causes table rewrite – introduce which DDL commands will cause table rewrite.
• Altering a column with minimal downtime – introduce how to alter a column with minimal downtime.

Section 13. Understanding PostgreSQL Constraints

• Primary key – illustrate how to define a primary key when creating a table or adding a primary key to an existing table.
• Foreign key – show you how to define foreign key constraints when creating a new table or add foreign key constraints for existing tables.
• CHECK constraint – add logic to check value based on a Boolean expression.
• UNIQUE constraint – make sure that values in a column or a group of columns are unique across the table.
• NOT NULL constraint – ensure values in a column are not NULL.

Section 14. PostgreSQL Data Types in Depth

• Boolean – store TRUE and FALSE values with the Boolean data type.
• CHAR, VARCHAR and TEXT – learn how to use various character types including CHAR, VARCHAR, and TEXT.
• NUMERIC – show you how to use NUMERIC type to store values that precision is required.
• DOUBLE PRECISION – learn to store inexact, variable-precision numbers in the database. The DOUBLE PRECISION type is also known as the FLOAT type.
• REAL – guide you on how to use single-precision floating-point numbers in the database.
• Integer – introduce you to various integer types in PostgreSQL including SMALLINT, INT and BIGINT.
• DATE – introduce the DATE data type for storing date values.
• Timestamp – understand timestamp data types quickly.
• Interval – show you how to use interval data type to handle a period of time effectively.
• TIME – use the TIME datatype to manage the time of day values.
• UUID – guide you on how to use UUID datatype and how to generate UUID values using supplied modules.
• Array – show you how to work with the array and introduces you to some handy functions for array manipulation.
• hstore – introduce you to data type which is a set of key/value pairs stored in a single value in PostgreSQL.
• JSON – illustrate how to work with JSON data type and shows you how to use some of the most important JSON operators and functions.
• User-defined data types – show you how to use the CREATE DOMAIN and CREATE TYPE statements to create user-defined data types.
• BYTEA – learn how to store binary strings in the database.

Section 15. Conditional Expressions & Operators

• CASE – show you how to form conditional queries with CASE expression.
• COALESCE – return the first non-null argument. You can use it to substitute NULL by a default value.
• NULLIF – return NULL if the first argument equals the second one.
• CAST – convert from one data type into another e.g., from a string into an integer, from a string into a date.

Section 16. PostgreSQL Utilities

• psql commands – show you the most common psql commands that help you interact with psql faster and more effectively.

Section 17. Troubleshooting

• Dealing with deadlocks – guide you on how to troubleshooting the deadlocks in PostgreSQL.
• Dealing with corrupted blocks – guide you on how to troubleshooting the corrupted blocks in PostgreSQL.
• Dealing with corrupt TOAST values – guide you on how to troubleshooting the corrupt TOAST values in PostgreSQL.
• Dealing with statistics corruption – guide you on how to troubleshooting the statistics corruption in PostgreSQL.
• Dealing with PL/pgSQL runtime errors – guide you on how to troubleshooting the PL/pgSQL runtime errors in PostgreSQL.
• How does page caches affect query performance? – guide you on how to troubleshooting the page caches in PostgreSQL.
• Dealing with transaction wraparound – guide you on how to troubleshooting the transaction wraparound in PostgreSQL.
• Examining backend memory usage – guide you on how to troubleshooting the backend memory usage in PostgreSQL.
• Logging backtrace for errors in functions – guide you on how to log backtrace for errors in specified functions.
• Reasons Partition Pruning Not Work – introduce you to the major reasons why the partition pruning not work in PostgreSQL.

Section 18. PostgreSQL Recipes

• How to compare two tables – describe how to compare data in two tables in a database.
• Compare Schema with pgAdmin 4 – guide you on how to use the Schema Diff feature provided by pgAdmin 4, to compare objects between two databases or two schemas.
• Migrate table data with DBeaver – describe how to use the DBeaver tool, to transfer data between different databases or between tables within the same database.
• How to delete duplicate rows in PostgreSQL – show you various ways to delete duplicate rows from a table.
• How to generate a random number in a range – illustrate how to generate a random number in a specific range.
• Basic PostGIS Queries with Geospatial Data – demonstrate basic PostGIS queries for working with geospatial data.

Advanced PostgreSQL Tutorial

This advanced PostgreSQL tutorial covers the advanced concepts including stored procedures, indexes, views, triggers, and database administrations.

PostgreSQL Functions

PostgreSQL provides a large number of functions for the built-in data types. This section shows you how to use the most commonly used PostgreSQL functions.

PostgreSQL PL/pgSQL

This PostgreSQL stored procedures section shows you step by step how to develop PostgreSQL user-defined functions using PL/pgSQL procedural language.

PostgreSQL Triggers

This section provides you with PostgreSQL trigger concept and shows how to manage triggers in PostgreSQL.

PostgreSQL Views

We will introduce you to the database views concept and show you how to manage views such as create, alter, and remove views from the database.

PostgreSQL Indexes

PostgreSQL indexes are effective tools to enhance database performance. Indexes help the database server find specific rows much faster than it could do without indexes.

PostgreSQL Optimization

This section introduce you to PostgreSQL performance optimization, and shows how to optimize performance in various scenarios in PostgreSQL.

PostgreSQL Administration

PostgreSQL administration covers the most important activities for PostgreSQL database server including roles and databases management, backup and restore.

PostgreSQL Monitoring

PostgreSQL monitoring covers the most important activities for PostgreSQL database server including monitoring and maintenance.

Application Programming Interfaces

This section shows you how to interact with the PostgreSQL database from the applications that use popular programming languages such as Java, Python, and PHP.

• PostgreSQL Java Tutorial – this PostgreSQL JDBC section shows you how to interact with the PostgreSQL databases using Java JDBC driver.
• PostgreSQL Python Tutorial – this PostgreSQL Python section shows you how to work with the PostgreSQL database using the Python programming language.
• Connect to PostgreSQL with Golang – guide you on how to work with the PostgreSQL database using the Golang programming language.