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 changing 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
- PostgreSQL Inner Join – selects rows from one table that have the corresponding rows in other tables.
- PostgreSQL Left Join – selects rows from one table that may or may not have the corresponding rows in other tables.
- PostgreSQL Full Outer Join – uses the full join to find a row in a table that does not have a matching row in another table.
- PostreSQL Cross Join – produces a Cartesian product of the rows in two or more tables.
- PostgreSQL Natural Join – joins two or more tables using implicit join condition based on the common column names in the joined table.
Section 5. Grouping data
- PostgreSQL Group By – divides rows into groups and applies an aggregate function on each group.
- PostgreSQL Having – applies the condition for groups divided by the GROUP BY clause.
Section 6. Performing set operations
- PostgreSQL Union – combines result sets of multiple queries into a single result set.
- PostgreSQL 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.
- PostgreSQL Except – returns the rows in the first query that does not appear in the output of the second query.
Section 7. Subquery
- PostgreSQL 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 DELETE statement.
- PostgreSQL Insert – inserts data into a table.
- PostgreSQL Update – updates existing data in a table.
- PostgreSQL Delete – deletes data in a 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 CREATE TABLE statement to create new tables. 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 TRUNCATE statement.
- PostgreSQL Data Types – covers the most commonly used PostgreSQL data types.
- PostgreSQL Create Table – creates new tables by using the CREATE TABLE statement.
- PostgreSQL Alter Table – changes existing table structure such as add or remove a column, set a new constraint for column and rename 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. Understanding PostgreSQL constraints
- PostgreSQL Primary Key – illustrates how to define primary key when creating a table or add primary keys to existing tables.
- PostgreSQL Foreign Key – shows you how to define foreign key constraints when creating a new table or add foreign key constraints for existing tables.
- PostgreSQL CHECK Constraint – to add logic to check value based on a Boolean expression.
- PostgreSQL UNIQUE Constraint – to make sure that a value in a column or a group of columns unique across the whole table.
- PostgreSQL NOT NULL Constraint – to ensure that values in a column are not NULL.
Section 12. PostgreSQL ways
This section covers some unique features of PostgreSQL including array, hstore, JSON, JSONB, etc.
- PostgreSQL array – shows you how to work with the array and introduces you to some handy functions for array manipulation.
- PostgreSQL hstore – introduces you to hstore data type which is a set of key/value pairs stored in a single value in PostgreSQL.
- PostgreSQL JSON – illustrates how to work with PostgreSQL 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.