PostgreSQL

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

COURSES 2

WATCH Brett Cassette's COURSE

Get Started With PostgreSQL

Get Started With PostgreSQL

It’s amazing how much you can accomplish with a basic knowledge of SQL. In this course we’ll learn how to store lots of informa...

14 lessons

WATCH Brett Cassette's COURSE

Using Postgres Window Functions

Using Postgres Window Functions

In this course we will show you how to wield the power behind the elusive window function. Which five movies were the highest g...

10 lessons

Browse all PostgreSQL lessons.

showing All 32 lessons...

Create a Postgres Table

P

Insert Data into Postgres Tables

P

Delete Postgres Records

P

Update Data in Postgres

P

Group and Aggregate Data in Postgres

P

Filter Data in a Postgres Table with Query Statements

P

Ensure Uniqueness in Postgres

P

Sort Postgres Tables

P

Use Foreign Keys to Ensure Data Integrity in Postgres

P

Create Foreign Keys Across Multiple Fields in Postgres

P

Enforce Custom Logic with Check Constraints in Postgres

P

Find Intersecting Data with Postgres’ Inner Join

P

Speed Up Postgres Queries with Indexes

P

Select Distinct Data in Postgres

P

Learn What A Window Function Can Do

P

Rank Records with Rank and Dense Rank

P

Perform Weighted Ranking with Percent Rank

P

Define Windows Using Multiple Factors

P

Find Percentiles Using ntile

P

Create Running Totals Using Window Functions

P

Understand Frame Clauses

P

Compare Related Rows Using Lag and Lead

P

Compare the Outliers Using First Value and Last Value

P

Combine Window Functions to Answer Complex Questions

P

Find Postgres Data That Doesn’t Match Join Conditions with Full Outer Join

See All Rows from Different Postgres Tables with Union All

P

Find Lonely Postgres Data with Left and Right Join

Join Tables in Postgres with Complex Conditions

P

Create a History Table in Postgres with Joins

P

Use Joins to Detect Orphaned Data in Postgres

P

Detect Duplicates in Postgres with Cartesian Product

P

Find Related Data with Inner Join in Postgres

P
postgres tutorial about Create a Postgres Table

Create a Postgres Table

1:45 postgres PRO

Learn how to create a table using the most widely-used data types (serial, varchar, integer, float, boolean, and date), and the most necessary constraints (NOT NULL and primary key).

postgres tutorial about Insert Data into Postgres Tables

Insert Data into Postgres Tables

4:24 postgres PRO

Learn how to insert one record, two records, a million records! We’ll also explore some edge cases, like where not all data is known at the time of insertion.

postgres tutorial about Delete Postgres Records

Delete Postgres Records

2:43 postgres PRO

What if you no longer need some information? Perhaps your tables are very large and you need to clean up your table to speed it up. This lesson will teach us how to delete data from our database.

postgres tutorial about Update Data in Postgres

Update Data in Postgres

1:55 postgres PRO

Data changes. In some applications, it changes all the time! In this lesson we’ll learn how to update a single record or many at once.

postgres tutorial about Group and Aggregate Data in Postgres

Group and Aggregate Data in Postgres

6:45 postgres PRO

How can we see a histogram of movies on IMDB with a particular rating? Or how much movies grossed at the box office each month? Or how many movies there are of each genre? These are examples of data aggregation questions, and this lesson will teach us how to answer them.

postgres tutorial about Filter Data in a Postgres Table with Query Statements

Filter Data in a Postgres Table with Query Statements

3:35 postgres PRO

We have all this data, but how do we answer questions about it? In this lesson we’ll learn how to filter down to just the information we’re looking for.

postgres tutorial about Ensure Uniqueness in Postgres

Ensure Uniqueness in Postgres

3:53 postgres PRO

Let’s say we have a bank. Our bank wants to give each account for each user a unique name, for instance, “Personal” or “Checking.” How can we make sure each account has a unique name for each user?

postgres tutorial about Sort Postgres Tables

Sort Postgres Tables

1:20 postgres PRO

How do I see a list of my Facebook friends sorted by the number of friends they have, and then sorted alphabetically to break ties? Sorting data helps us to answer important business questions. In this lesson, we’ll learn how to do it in Postgres.

postgres tutorial about Use Foreign Keys to Ensure Data Integrity in Postgres

Use Foreign Keys to Ensure Data Integrity in Postgres

2:18 postgres PRO

Every movie needs a director and every rented movie needs to exist in the store. How do we make sure something in another table exists before inserting new data? This lesson will teach us about foreign keys and references.

postgres tutorial about Create Foreign Keys Across Multiple Fields in Postgres

Create Foreign Keys Across Multiple Fields in Postgres

3:08 postgres PRO

What if your foreign keys are more complex than a single field? Postgres lets you enforce foreign key constraints across multiple fields with a similar syntax to defining complex primary keys.

postgres tutorial about Enforce Custom Logic with Check Constraints in Postgres

Enforce Custom Logic with Check Constraints in Postgres

2:07 postgres PRO

What if your tables need custom constraints? A movie should only receive 1-5 stars, never 0 or 6. Price after tax should always equal the sum of the price plus tax. Check constraints help you enforce your own custom logic.

postgres tutorial about Find Intersecting Data with Postgres’ Inner Join

Find Intersecting Data with Postgres’ Inner Join

4:26 postgres PRO

You have a table of movies and a table of directors; how can you see which director created each movie? An inner join will link related records so that you can answer questions like these. Inner joins are the most commonly used SQL join, because they only return data that is common in both tables.

postgres tutorial about Speed Up Postgres Queries with Indexes

Speed Up Postgres Queries with Indexes

2:33 postgres PRO

The more data you add, the longer it will take to answer new questions you pose to your database. That’s because so far, Postgres has been scanning through the table looking at every row to see if it meets the constraint. We can speed this process up substantially by adding indexes.

postgres tutorial about Select Distinct Data in Postgres

Select Distinct Data in Postgres

0:52 postgres PRO

How many users visited our site yesterday? We can find out by looking at each visit, and limiting this list to a distinct list of users (who may have visited many times). In this lesson we’ll learn how to answer questions like these with the distinct keyword.

postgres tutorial about Learn What A Window Function Can Do

Learn What A Window Function Can Do

1:47 postgres PRO

In this lesson we’ll see how we can partition movies into the years they were released, and find the top 5 rated titles each year. The power of the window function is to partition data into groups and answer questions about those groups.

postgres tutorial about Rank Records with Rank and Dense Rank

Rank Records with Rank and Dense Rank

1:35 postgres PRO

If you have a list of competitors in a race, and a list of their finishing times, how do you create a list of what place they came in? Rank and dense rank can help you out; they differ in how they handle ties.

postgres tutorial about Perform Weighted Ranking with Percent Rank

Perform Weighted Ranking with Percent Rank

1:55 postgres PRO

What if you had a list of student grades and wanted to give them a weighted grade for the school year? Percent rank is used to answer frequency distribution questions.

postgres tutorial about Define Windows Using Multiple Factors

Define Windows Using Multiple Factors

1:55 postgres PRO

Weighted ranking on a single factor can hide lots of information. For instance, students without access to internet perform 1-3 full letter grades worse than their peers with internet access. A teacher that wanted to consider student privilege could take many factors into account when assigning student grades to get a better sense for how strong individual students were.

postgres tutorial about Find Percentiles Using ntile

Find Percentiles Using ntile

1:44 postgres PRO

What scores do the 90th percentile of students receive? How long does the 99th percentile web request take? Percentiles and arbitrary ntiles provider a richer understanding of underlying data and outliers.

postgres tutorial about Create Running Totals Using Window Functions

Create Running Totals Using Window Functions

1:06 postgres PRO

How could your Kickstarter-type site list the running total for each contribution next to it? Using window functions of course! The default behavior for aggregations under window functions is to create running totals.

postgres tutorial about Understand Frame Clauses

Understand Frame Clauses

2:00 postgres PRO

What if you want aggregations that aren’t running totals? What if you want the sum total for the whole frame? Or the minimum value across the frame? Unbounded preceding, unbounded following, and current row help you define your frame of reference.

postgres tutorial about Compare Related Rows Using Lag and Lead

Compare Related Rows Using Lag and Lead

2:16 postgres PRO

If you have a list of competitors and their finishing times, how do you determine how much time separated each competitor? Lag and lead allow you to compare rows in order -- for instance, comparing the 1st and 2nd place finishers and the 2nd and 3rd place finishers.

postgres tutorial about Compare the Outliers Using First Value and Last Value

Compare the Outliers Using First Value and Last Value

1:55 postgres PRO

How does each runner compare with the first and last place finisher? What about the nth place finisher? Firstvalue, lastvalue, and nth_value can make these decisions.

postgres tutorial about Combine Window Functions to Answer Complex Questions

Combine Window Functions to Answer Complex Questions

3:38 postgres PRO

Individual window functions are useful, but sometimes you’ll need to combine them to answer complex questions. For instance, which 10 star movies were released in the year that had the most 10 star movies released?

postgres tutorial about Find Postgres Data That Doesn’t Match Join Conditions with Full Outer Join

Find Postgres Data That Doesn’t Match Join Conditions with Full Outer Join

1:14 postgres

What if we want to see all Kickstarter projects with backers, plus projects that have no backers, plus backers that have no projects? We can use full outer join for that. In the wild, full outer join, left join, and right join will most often be used for orphan detection.

postgres tutorial about See All Rows from Different Postgres Tables with Union All

See All Rows from Different Postgres Tables with Union All

1:16 postgres PRO

By default, union removes duplicate rows in the combined table. Union all, however, leaves all rows intact.

postgres tutorial about Find Lonely Postgres Data with Left and Right Join

Find Lonely Postgres Data with Left and Right Join

1:59 postgres

How can we find out which Kickstart projects have no backers? Or which backers have not backed any projects? Left and right join can answer these questions.

postgres tutorial about Join Tables in Postgres with Complex Conditions

Join Tables in Postgres with Complex Conditions

2:38 postgres PRO

While our data is sometimes nicely managed with surrogate primary key joins, often times older tables can feature join conditions with complex natural keys.

postgres tutorial about Create a History Table in Postgres with Joins

Create a History Table in Postgres with Joins

2:22 postgres PRO

Possibly my favorite data management trick is the history table. With history tables, we record all changes in two locations -- once in the primary table (as an update), and once in a history table (as an insert). With this handy trick, we save an audited record of all data, when it was live, and who changed it and when. Coupled with non-destructive deletes, you can explore the complete history of your data with joins.

postgres tutorial about Use Joins to Detect Orphaned Data in Postgres

Use Joins to Detect Orphaned Data in Postgres

2:02 postgres PRO

Unfortunately, sometimes we don’t add the right constraints to our data. A user gets deleted and old data doesn’t get cleaned up. Primary information is deleted, but its audit trail isn’t. With left and right joins, we can flag anomalous data for deletion.

postgres tutorial about Detect Duplicates in Postgres with Cartesian Product

Detect Duplicates in Postgres with Cartesian Product

1:11 postgres PRO

The cartesian product is a humdinger -- it relates all data in one table to all data in another! Now that’s a lot of rows, and fast. Surprisingly, cartesian products are as fast as inner joins at duplicate detection, thanks to the magic of the query planner!

postgres tutorial about Find Related Data with Inner Join in Postgres

Find Related Data with Inner Join in Postgres

2:52 postgres PRO

In this lesson, we’ll learn how to relate data on a condition that exists in multiple tables. The inner join will help us answer the question “Which users have backed our Kickstarter project?” We’ll also see what join conditions are all about.

HEY, QUICK QUESTION!
Joel's Head
Why are we asking?