Welcome to the PostgreSQLTutorial.com website! This PostgreSQL tutorial helps you understand PostgreSQL quickly. You will learn PostgreSQL fast through many practical examples. We will show you not only problems but also how to solve them creatively in PostgreSQL.
If you are…
- Looking for learning PostgreSQL fast and easily.
- Developing applications using PostgreSQL as the back-end database management system.
- Migrating from other database management systems such as MySQL, Oracle, Microsoft SQL Server to PostgreSQL.
You will find all you need to know to get started with the PostgreSQL quickly and effectively here on our website.
We developed the PostgreSQL tutorial to demonstrate the unique features of PostgreSQL that make it the most advanced open source database management system.
Basic PostgreSQL Tutorial
First, you will learn how to query data from a single table using basic data selection techniques such as selecting columns, sorting result set, and filtering rows. Then, you will learn about the 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 new a table or modifying an existing table’s structure.
Section 1. Getting Started with PostgreSQL
If you are new to PostgreSQL, follow 3-easy steps to get started quickly with PostgreSQL.
- First, get a brief overview of PostgreSQL to understand what PostgreSQL is.
- Second, install PostgreSQL to your local computer and connect to PostgreSQL database server from a client application such as psql or pgAdmin.
- Third, download the PostgreSQL sample database and load it into the PostgreSQL database server.
Section 2. Querying Data
- Select – shows you how to query data from a single table.
- Order By – guides you on how to sort the result set returned from a query.
- Select Distinct – provides you a clause that removes duplicate rows in the result set.
Section 3. Filtering Data
- Where – filters rows based on a specified condition.
- Limit – gets a subset of rows generated by a query.
- Fetch– limits the number of rows returned by a query.
- In – selects data that matches any value in a list of values.
- Between – selects data that is a range of values.
- Like – filters data based on pattern matching.
- Is Null – checks if a value is null or not.
- Table & column aliases – describes how to use table and column aliases in the query.
Section 4. Joining Multiple Tables
- Joins – shows you a brief overview of joins in PostgreSQL.
- Inner Join – selects rows from one table that have the corresponding rows in other tables.
- Left Join – selects rows from one table that may or may not have the corresponding rows in other tables.
- Self-join – joins a table to itself by comparing a table to itself.
- Full Outer Join – uses the full join to find a row in a table that does not have a matching row in another table.
- Cross Join – produces a Cartesian product of the rows in two or more tables.
- Natural Join – joins two or more tables using implicit join condition based on the common column names in the joined tables.
Section 5. Grouping Data
- Group By – divides rows into groups and applies an aggregate function on each.
- Having – applies the condition for groups.
Section 6. Performing Set Operations
- Union – combines result sets of multiple queries into a single result set.
- Intersect – combines the result sets of two or more queries and returns a single result set that has the rows appear in both result sets.
- Except – returns the rows in the first query that does not appear in the output of the second query.
Section 7. Grouping Sets
- 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 an subtotals.
Section 8. Subquery
- Subquery – writes a query nested inside another query.
- ANY – retrieves 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 – checks for the existence of rows returned by a subquery.
Section 9. Common Table Expressions
- PostgreSQL CTE – introduction to PostgreSQL common table expressions or CTEs.
- Recursive query using CTEs – discusses the recursive query and learns how to apply it in various contexts.
Section 10. 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. In addition, you learn how to use the upsert statement to merge data.
- Insert – inserts data into a table.
- Update – updates existing data in a table.
- Update join – updates values in a table based on values in another table.
- Delete – deletes data in a table.
- Upsert – inserts or update data if the new row already exists in the table.
Section 11. Transactions
- PostgreSQL Transactions – shows you how to handle transactions in PostgreSQL using BEGIN, COMMIT, and ROLLBACK statements.
Section 12. Import & Export Data
You will learn how to import and export PostgreSQL data from and to CSV file format using the copy command.
- Import CSV file into Table – shows you how to import CSV file into a table.
- Export PostgreSQL Table to CSV file – shows you how to export tables to a CSV file.
Section 13. Managing Databases
In this section, you will learn how to manage databases in PostgreSQL including creating databases, modifying existing database’s features and deleting databases.
- Create Database – creates a new database using
- Alter Database – modifies the features of an existing database using the
- Rename Database – changes the name of the database to a new one.
- Drop Database – removes a database permanently using
- Copy a Database – copies a database within a database server or from a server to another.
- Get Database Object Sizes – introduces you to various handy functions to get the size of a database, a table, and indexes.
Section 14. Managing Tables
In this section, we start exploring the PostgreSQL data types and showing you how to use
CREATE TABLE statement to create a new table. We will also cover some additional features, such as modifying table structure and deleting tables. In addition, you will learn an efficient way to delete all rows from a table by using the
- Data types – covers the most commonly used PostgreSQL data types.
- Create table – guides 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 – shows you how to use the identity column.
- Alter table – changes the structure of an existing table.
- Rename table – change the name of the table to a new one.
- Add column – shows you how to use add one or more columns to an existing table.
- Drop column – demonstrates how to drop a column of a table.
- Change column data type – shows you how to change the data of a column.
- Rename column – illustrates how to rename one or more column of a table.
- Drop table – removes an existing table and all of its dependent objects.
- Truncate table – removes all data in a large table quickly and efficiently.
- Temporary table – shows you how to use the temporary table.
- Copy a table – shows you how to copy a table to a new one.
Section 15. Understanding PostgreSQL Constraints
- Primary key – illustrates how to define a primary key when creating a table or add a primary key to an existing table.
- Foreign key – shows you how to define foreign key constraints when creating a new table or add foreign key constraints for existing tables.
- CHECK constraint – adds logic to check value based on a Boolean expression.
- UNIQUE constraint – makes sure that values in a column or a group of columns unique across the table.
- NOT NULL constraint – ensures values in a column are not
Section 16. PostgreSQL Data Types in Depth
- Boolean – stores
FALSEvalues with the Boolean data type.
- CHAR, VARCHAR and TEXT – learns how to use various character types including
- NUMERIC – shows you how to use
NUMERICtype to store values that precision is required.
- Integer – introduces you various integer types in PostgreSQL including
- DATE – introduces the
DATEdata type for storing date values.
- Timestamp – understands timestamp data types quickly.
- Interval – shows you how to use interval data type to handle a period of time effectively.
- TIME – uses the
TIMEdata type to manage the time of day values.
- UUID – guides you to use
UUIDdata type and how to generate
UUIDvalues using supplied modules.
- Array – shows you how to work with the array and introduces you to some handy functions for array manipulation.
- hstore – introduces you to data type which is a set of key/value pairs stored in a single value in PostgreSQL.
- JSON – illustrates 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 – shows you how to use the
CREATE TYPEstatements to create user-defined data types.
Section 17. Managing Schemas
- Schema – introduces the schema concept and explains how the schema search path works in PostgreSQL.
- Create Schema – shows you how to create a new schema in a database.
- Alter Schema – renames a schema or changes its owner to the new one.
- Drop schema – deletes one or more schemas with their objects from a database.
Section 18. Conditional Expressions & Operators
CASE– shows you how to form conditional queries with
COALESCE– returns the first non-null argument. You can use it to substitute
NULLby a default value.
NULLif the first argument equals the second one.
CAST– converts from one data type into another e.g., from a string into an integer, from a string into a date.
Section 19. PostgreSQL Utilities
- psql commands – shows you the most common psql commands that help you interact with psql faster and more effectively.
Section 20. PostgreSQL Recipes
- How to compare two tables – describes how to compare data in two tables in a database.
- How to delete duplicate rows in PostgreSQL – shows you various ways to delete duplicate rows from a table.
- How to generate a random number in a range – illustrates how to generate a random number in a specific range.
- EXPLAIN statement– guides you on how to use the
EXPLAINstatement to return the execution plan of a query.
- PostgreSQL vs. MySQL – compares PostgreSQL with MySQL in terms of functionalities.
PostgreSQL Programming Interfaces
You typically use PostgreSQL as the back-end database of a specific application. To interact with the PostgreSQL databases from the application, you need a database driver. PostgreSQL provides you with various database drivers that support the most popular programming languages such as PHP, Java, Python, C#, and C/C++.