🐘 PostgreSQL Basics

The best flavor of SQL

← Back to Notes

Why Use PostgreSQL

PostgreSQL (pronounced "Postgres-Q-L") is a powerful, open-source relational database management system that offers unique advantages for modern development.

  • Dual Query Support: PostgreSQL supports both SQL for relational and JSON for non-relational querying, making it versatile for different data models.
  • Multi-Language Support: PostgreSQL supports many popular programming languages. This guide focuses on Python integration for practical application development.
  • ACID Compliance: Full support for transactions, ensuring data integrity and consistency.
  • Advanced Features: Supports advanced data types, full-text search, and complex queries.

Tutorial Sample Database

We'll use a DVD rental database to demonstrate PostgreSQL features. This database represents a typical business scenario with realistic data relationships.

Database Breakdown

The DVD rental database represents the business processes of a DVD rental store and contains:

  • 15 tables
  • 1 trigger
  • 7 views
  • 8 functions
  • 1 domain
  • 13 sequences

DVD Rental ER Model

DVD Rental Database ER Diagram

DVD Rental Database Tables

The database contains 15 interconnected tables:

  • actor – Stores actor data including first name and last name.
  • film – Stores film data such as title, release year, length, rating, etc.
  • film_actor – Stores the relationships between films and actors.
  • category – Stores film's categories data.
  • film_category – Stores the relationships between films and categories.
  • store – Contains the store data including manager staff and address.
  • inventory – Stores inventory data.
  • rental – Stores rental data.
  • payment – Stores customer's payments.
  • staff – Stores staff data.
  • customer – Stores customer data.
  • address – Stores address data for staff and customers.
  • city – Stores city names.
  • country – Stores country names.

DVD Sample Database Download

Click here to download the sample database.

Loading the Database

Assuming you have already installed PostgreSQL, loading the database is straightforward:

# 1. Connect to the PostgreSQL server
psql -U postgres
# 2. Create the dvdrental database
CREATE DATABASE dvdrental;
# 3. Verify database creation
\l
# 4. Exit psql
exit

Restore the Sample Database from Archive

# 5. Extract the .tar file and note the path
# 6. Restore the database using pg_restore
pg_restore -U postgres -d dvdrental "{path to archive}\dvdrental.tar"

Verify the Sample Database

# 7. Connect back to the server
psql -U postgres
# 8. Switch to the dvdrental database
\c dvdrental
# 9. Display all tables
\dt

Querying Data

The SELECT Clause

The SELECT statement is one of the most complex and flexible statements in PostgreSQL. It supports many clauses for forming sophisticated queries.

SELECT Clause Options
  • Select distinct rows using DISTINCT
  • Sort rows using ORDER BY
  • Filter rows using WHERE
  • Select a subset of rows using LIMIT or FETCH
  • Group rows using GROUP BY
  • Filter groups using HAVING
  • Join tables using INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN
  • Perform set operations using UNION, INTERSECT, EXCEPT

Basic SELECT syntax:

SELECT
    select_list
FROM
    table_name;
-- or equivalently:
SELECT select_list FROM table_name;
PostgreSQL SELECT evaluation order

Examples using the sample database:

-- Select a single column
SELECT first_name FROM customer;
-- Select multiple columns
SELECT first_name, last_name FROM actor;
-- Select all columns (avoid in production)
SELECT * FROM rental;

You can also use SELECT without a FROM clause for special cases:

-- Selecting constants
SELECT 1;
SELECT 'Hello, world!';
SELECT 3.14 * 2;
-- Using functions
SELECT now();
SELECT current_date;
SELECT random();
-- Performing calculations
SELECT 5 + 10;
SELECT length('PostgreSQL');
-- Using built-in functions
SELECT version();
SELECT pg_backend_pid();

Concatenating columns with expressions:

-- Concatenate first and last name
SELECT first_name || ' ' || last_name, email FROM customer;

Column Aliases

Use the AS keyword to create meaningful column names:

-- Using the AS keyword (recommended)
SELECT first_name || ' ' || last_name AS full_name, email FROM customer;
-- Alias for a single column
SELECT first_name AS given_name FROM customer;
-- Alias with spaces (use double quotes)
SELECT first_name || ' ' || last_name AS "Full Name" FROM customer;

The ORDER BY Clause

Sort query results using ORDER BY:

SELECT
    select_list
FROM
    table_name
ORDER BY
    sort_expression1 [ASC | DESC],
    sort_expression2 [ASC | DESC];
PostgreSQL SELECT with ORDER BY evaluation order

Examples:

-- Sort by a single column (ascending by default)
SELECT first_name, last_name FROM customer ORDER BY first_name;
-- Sort by multiple columns
SELECT first_name, last_name FROM customer
ORDER BY first_name ASC, last_name DESC;
-- Sort by an expression
SELECT first_name, LENGTH(first_name) AS len FROM customer
ORDER BY len DESC;
-- Handle null values
SELECT first_name, last_name FROM customer
ORDER BY first_name NULLS FIRST;

The SELECT DISTINCT Clause

Remove duplicate rows from results:

-- Distinct on a single column
SELECT DISTINCT column1 FROM table_name;
-- Distinct on multiple columns
SELECT DISTINCT column1, column2 FROM table_name;
-- Distinct on all columns
SELECT DISTINCT * FROM table_name;

Example:

SELECT DISTINCT rental_rate FROM film ORDER BY rental_rate;

Filtering Data

The WHERE Clause

Filter rows that satisfy specific conditions:

SELECT select_list FROM table_name
WHERE condition
ORDER BY sort_expression;
PostgreSQL SELECT with WHERE evaluation order

Comparison and logical operators:

Operator Description
=Equal
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
<> or !=Not equal
ANDLogical operator AND
ORLogical operator OR
INMatch any value in a list
BETWEENValue within a range
LIKEPattern matching
IS NULLCheck for NULL values
NOTNegate other operators

Examples:

-- Equality condition
SELECT last_name, first_name FROM customer WHERE first_name = 'Jamie';
-- Multiple conditions with AND
SELECT last_name, first_name FROM customer
WHERE first_name = 'Jamie' AND last_name = 'Rice';
-- Multiple conditions with OR
SELECT first_name, last_name FROM customer
WHERE last_name = 'Rodriguez' OR first_name = 'Adam';
-- Using the IN operator
SELECT first_name, last_name FROM customer
WHERE first_name IN ('Ann', 'Anne', 'Annie');
-- Pattern matching with LIKE
SELECT first_name, last_name FROM customer
WHERE first_name LIKE 'Ann%';
-- Combining conditions
SELECT first_name, LENGTH(first_name) AS name_length FROM customer
WHERE first_name LIKE 'A%' AND LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY name_length;
-- Not equal operator
SELECT first_name, last_name FROM customer
WHERE first_name LIKE 'Bra%' AND last_name != 'Motley';

The AND Operator

PostgreSQL supports standard boolean values: true, false, and null:

-- Boolean representations
-- true: true, t, y, yes, 1
-- false: false, f, n, no, 0
SELECT 1 = 1 AS result; -- returns 't' for true
-- AND truth table
SELECT true AND true AS result;   -- true
SELECT true AND false AS result;  -- false
SELECT false AND false AS result; -- false
SELECT true AND null AS result;   -- null

Example:

SELECT title, length, rental_rate FROM film
WHERE length > 180 AND rental_rate < 1;

The OR Operator

The OR operator follows standard truth table logic:

-- OR truth table
SELECT true OR true AS result;   -- true
SELECT true OR false AS result;  -- true
SELECT false OR false AS result; -- false
SELECT true OR null AS result;   -- true
SELECT false OR null AS result;  -- null

Example:

SELECT title, rental_rate FROM film
WHERE rental_rate = 0.99 OR rental_rate = 2.99;

The LIMIT Clause

Constrain the number of rows returned:

SELECT select_list FROM table_name
ORDER BY sort_expression
LIMIT row_count;

With OFFSET to skip rows:

SELECT select_list FROM table_name
ORDER BY sort_expression
LIMIT row_count OFFSET row_to_skip;

Examples:

-- Get the first 5 films
SELECT film_id, title, release_year FROM film
ORDER BY film_id LIMIT 5;
-- Skip 3 rows, then get the next 4 rows
SELECT film_id, title, release_year FROM film
ORDER BY film_id LIMIT 4 OFFSET 3;
-- Get the top 10 most expensive films
SELECT film_id, title, rental_rate FROM film
ORDER BY rental_rate DESC LIMIT 10;

The FETCH Clause

The SQL standard alternative to LIMIT:

OFFSET row_to_skip { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

Examples:

-- Get the first row
SELECT film_id, title FROM film
ORDER BY title
FETCH FIRST 1 ROW ONLY;
-- Skip 5 rows, get the next 5
SELECT film_id, title FROM film
ORDER BY title
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY;

The IN and NOT IN Operators

Check if a value matches any value in a list:

-- IN operator syntax
value IN (value1, value2, ...)
-- NOT IN operator syntax
value NOT IN (value1, value2, ...)

Examples:

-- Films with specific IDs
SELECT film_id, title FROM film WHERE film_id IN (1, 2, 3);
-- Customers with specific names
SELECT first_name FROM customer
WHERE first_name IN ('Ryan', 'Tom', 'Bob');
-- Films NOT in specific IDs
SELECT film_id, title FROM film WHERE film_id NOT IN (1, 2, 3);
-- Customers NOT with specific names
SELECT first_name FROM customer
WHERE first_name NOT IN ('Ryan', 'Tom', 'Bob');

The BETWEEN Operator

Check if a value is within a range:

-- BETWEEN syntax (inclusive)
value BETWEEN low AND high
-- equivalent to: value >= low AND value <= high
-- NOT BETWEEN syntax
value NOT BETWEEN low AND high
-- equivalent to: value < low OR value > high

Examples:

-- Payments within an ID range
SELECT payment_id, amount FROM payment
WHERE payment_id BETWEEN 17503 AND 17505
ORDER BY payment_id;
-- Payments outside an ID range
SELECT payment_id, amount FROM payment
WHERE payment_id NOT BETWEEN 17503 AND 17505
ORDER BY payment_id;
-- Payments within a date range and amount condition
SELECT payment_id, amount, payment_date FROM payment
WHERE payment_date BETWEEN '2007-02-15' AND '2007-02-20'
AND amount > 10
ORDER BY payment_date;

The LIKE Operator

Pattern matching with wildcards:

  • % - Matches any sequence of zero or more characters.
  • _ - Matches any single character.
-- Basic LIKE syntax
value LIKE pattern
value NOT LIKE pattern
-- Case-insensitive matching
value ILIKE pattern

Examples:

-- Names starting with 'Jen'
SELECT first_name, last_name FROM customer
WHERE first_name LIKE 'Jen%';
-- Names containing 'er'
SELECT first_name, last_name FROM customer
WHERE first_name LIKE '%er%'
ORDER BY first_name;
-- Names with a specific pattern (4 chars, 3rd char is 'e')
SELECT first_name, last_name FROM customer
WHERE first_name LIKE '_her%'
ORDER BY first_name;
-- Names NOT starting with 'Jen'
SELECT first_name, last_name FROM customer
WHERE first_name NOT LIKE 'Jen%'
ORDER BY first_name;

The IS NULL Operator

Check for null values (cannot use = or != with null):

-- Check for null values
value IS NULL
-- Check for non-null values
value IS NOT NULL

Examples:

-- Find customers with no phone number
SELECT first_name, last_name, phone FROM customer
WHERE phone IS NULL;
-- Find customers with phone numbers
SELECT first_name, last_name, phone FROM customer
WHERE phone IS NOT NULL;