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 easy.
- 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.
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 data selection techniques such as joining multiple tables, using set operations and constructing the subquery. Finally, you will learn how to manage database table such as creating new tables and modifying tables’ 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
- PostgreSQL Select statement – selects data from a table.
- PostgreSQL Order By – sorts the result set returned by the SELECT statement.
- PostgreSQL Select Distinct – removes duplicate rows in the result set.
Section 3. Filtering data
- PostgreSQL Where – filters rows based on a specified condition.
- PostgreSQL Limit – gets a subset of rows generated by a query.
- PostgreSQL In – selects data that matches any value in a list of values.
- PostgreSQL Between – selects data that is a range of values.
- PostgreSQL Like – filters data based on pattern matching.
Section 4. Joining multiple tables
- 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.
- 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 group.
- Having – applies the condition for groups divided by the GROUP BY clause.
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. Subquery
- Subquery – writes a query nested inside another query.
Section 8. Modifying data
In this section, you will learn how to insert data into a table with the
INSERT statement, modify existing data with
UPDATE statement, and remove data with the
- Insert – inserts data into a table.
- Update – updates existing data in a table.
- Delete – deletes data in a table.
- Upsert – inserts or update data if the new row already exists in the table.
Section 9. PostgreSQL import & export
You will learn how to import and export PostgreSQL data from and to CSV file format using 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 10. Managing tables
In this section, we start exploring the PostgreSQL data types and showing you how to use the
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
- PostgreSQL Data Types – covers the most commonly used PostgreSQL data types.
- PostgreSQL Create Table – creates new tables by using the
- PostgreSQL Alter Table – changes existing table structure such as add or remove a column, set a new constraint for column and rename table.
- PostgreSQL Add Column – shows you how to use add one or more columns to an existing table.
- PostgreSQL Drop Table – removes existing table and its dependent objects e.g., views, constraints, etc., from the database.
- PostgreSQL Truncate Table – removes all data from large tables quickly.
Section 11. PostgreSQL data types in depth
- Boolean – stores
FALSEvalues with 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 integertypes in PostgreSQL including
- SERIAL – shows you how to create auto-increment column using
- DATE – introduces the
DATEdata type for storing date values.
- Timestamp – understands timestamp data types quickly.
- UUID – guides you to use
UUIDdata type and how to generate
UUIDvalues using supplied modules.
Section 12. Understanding PostgreSQL constraints
- Primary Key – illustrates how to define primary key when creating a table or add primary keys to existing tables.
- 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 a value in a column or a group of columns unique across the table.
- NOT NULL Constraint – ensures values in a column are not NULL.
Section 13. PostgreSQL ways
This section covers some unique features of PostgreSQL including array, hstore, JSON, JSONB, etc.
- Array – shows you how to work with the array and introduces you to some handy functions for array manipulation.
- PostgreSQL hstore – introduces you to
hstoredata type which is a set of key/value pairs stored in a single value in PostgreSQL.
- PostgreSQL JSON – illustrates how to work with JSON data type and shows you how to use some of the most important PostgreSQL JSON operators and functions.
- psql commands – shows you the most common psql commands that help you interact with psql faster and more effective.
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 many database drivers for supporting popular programming languages such as PHP, Java, Python, C#, C/C++, etc.