PostgreSQL

psql

Create the Database

DROP DATABASE IF EXISTS postgresql_example;

CREATE DATABASE postgresql_example;

\connect postgresql_example;

Create the Tables

DROP TABLE IF EXISTS people_hobbies;
DROP TABLE IF EXISTS hobbies;
DROP TABLE IF EXISTS people;

CREATE TABLE people(
  id      SERIAL PRIMARY KEY,
  name    TEXT,
  state   TEXT
);

CREATE TABLE hobbies(
  id        SERIAL PRIMARY KEY,
  name      TEXT,
  category  TEXT
);

CREATE TABLE people_hobbies(
  id        SERIAL PRIMARY KEY,
  people_id SMALLINT,
  hobbies_id SMALLINT,
  FOREIGN KEY (people_id) REFERENCES people (id),
  FOREIGN KEY (hobbies_id) REFERENCES hobbies (id)
);

Add Data to Tables

INSERT INTO people (name, state)
VALUES ('Grant', 'Colorado'),
       ('Connor', 'Arizona'),
       ('Evan', 'Florida'),
       ('Michael', 'North Carolina');

INSERT INTO hobbies (name, category)
VALUES ('Playing Pickleball', 'Exercise'),
       ('Playing Video Games', 'Hobbies'),
       ('Hiking', 'Exercise'),
       ('Watching TV', 'Leisure');

INSERT INTO people_hobbies (people_id, hobbies_id)
VALUES ('1', '2'),
       ('1', '3'),
       ('2', '2'),
       ('3', '4'),
       ('4', '2'),
       ('4', '4');

The -f flag means "file."

psql -f postgresql_example.sql

ON DELETE CASCADE

When a row is deleted from the parent table, all rows in the child table that reference that parent record are also automatically deleted.

ON UPDATE CASCADE

If the primary key of a parent record is updated, the foreign key values in all related child records are automatically updated to match the new value.

ON INSERT CASCADE

Can be simulated using Triggers to automatically insert related data into a child table when a new parent record is created.

\COPY majors(id, name, credits_required, minimum_gpa) FROM 'majors.csv' delimiter ',' CSV HEADER null 'null';

\COPY students(id, name, major) FROM 'students.csv' delimiter ',' CSV HEADER null 'null';

\COPY enrollments(student_id, course_name, credit_hours, start_date, end_date, cost, letter_grade) FROM 'enrollments.csv' delimiter ',' CSV HEADER null 'null';

\list -- list databases

\du -- display users

\dt -- display tables

\quit

Numbers

• SMALLINT - 2 bytes Whole numbers ranging from -32,768 to +32,767.
• INTEGER / INT - 4 bytes Whole numbers ranging from -2,147,483,648 to +2,147,483,647.
• BIGINT - 8 bytes Whole numbers ranging from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.
• REAL - 4 bytes Floating-point number with ~6 decimal digits of precision.
• DOUBLE PRECISION - 8 bytes Floating-point number with ~15 decimal digits of precision.
• NUMERIC / DECIMAL - Variable (typically 5–17+ bytes) Exact numeric value with user-defined precision and scale.
• SMALLSERIAL - 2 bytes Auto-incrementing integer ranging from 1 to 32,767.
• SERIAL - 4 bytes Auto-incrementing integer ranging from 1 to 2,147,483,647.
• BIGSERIAL - 8 bytes Auto-incrementing integer ranging from 1 to 9,223,372,036,854,775,807.

Text & Strings

• VARCHAR(20) - Up to 21 bytes (20 characters + 1-byte length header for short strings). Variable-length string.
• CHAR(2) - 3 bytes (2 characters + 1-byte header). Fixed-length string padded with spaces.
• TEXT - Variable (1–4 byte header + data). Practically unlimited length.

Boolean

• BOOLEAN - 1 byte True or false.

Dates

• DATE - 4 bytes Calendar date.
• TIME - 8 bytes Time without time zone.

IDs

• UUID - 16 bytes Universally unique identifier.

Data Types

• JSON - Variable Text representation of JSON data.
• JSONB - Variable Binary JSON format with additional storage overhead but faster querying.

One-to-One

employees(
  id SERIAL PRIMARY KEY,
  name VARCHAR
)

parking_spots(
  id SERIAL PRIMARY KEY
  employee_id INT UNIQUE,
  FOREIGN KEY (employee_id) REFERENCES employees (id)
)

Each record in Table A links to exactly one record in Table B, and vice versa.

Often used for separating sensitive data or for organizational purposes, though sometimes data is kept in a single table.

Example - An employee can have one unique parking spot, and one parking spot is tied to only one employee.

One-to-Many

customers(
  id SERIAL PRIMARY KEY,
  name VARCHAR
)

orders(
  id SERIAL PRIMARY KEY
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
)

One record in Table A can link to multiple records in Table B, but a record in Table B links to only one in Table A.

The primary key from Table A becomes a foreign key in Table B.

Example - A customer can have many orders, and orders only have one customer.

Many-to-Many

students(
  id SERIAL PRIMARY KEY,
  name VARCHAR
)

courses(
  id SERIAL PRIMARY KEY,
  name VARCHAR
)

enrollments(
  id SERIAL PRIMARY KEY
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students (id),
  FOREIGN KEY (course_id) REFERENCES courses (id),
  UNIQUE (student_id, course_id)
)

Records in Table A can link to many records in Table B, and records in Table B can link to many records in Table A.

Many-to-many relationships are implemented using a junction (join) table that stores foreign keys referencing both tables. The join table may consist only of these foreign keys (with a composite primary key), or it may include additional attributes and a surrogate primary key when the relationship itself has data or identity.

• Pure Many-to-Many Tables - Only for the relationship. Often only contain foreign keys
• Association Tables - When the relationship matters. Often includes additional attributes, and their own id.

Example - Students can enroll in many courses, and a course can have many students.

All

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
uid uid UUID NOT NULL UNIQUE,
created_by
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
updated_by
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
deleted_at TIMESTAMP NULL

Users

first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(255) UNIQUE NOT NULL,
phone_number VARCHAR(20),
address_line1 VARCHAR(100),
address_line2 VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
postal_code VARCHAR(20),
country CHAR(2)

Accounts

It's best to not store the security questions as plain text. It's best to store them as a hashed answer, just like the password.

username TEXT NOT NULL UNIQUE,
password_digest TEXT NOT NULL,
security_question TEXT NOT NULL,
security_question_answer TEXT NOT NULL

Roles

It's best to implement a roles table, a permissions table, and a role_permissions table.

name
permissions

Migrations are files that describe schema changes.

ALTER TABLE users
ADD COLUMN bio TEXT;

SELECT (DISTINCT) - What vertical columns do you want to select from the table? This can be all * or it can be specific ones like name or id. Adding DISTINCT eliminates any duplicates from the query.

FROM - What table do you want to receive data from?

JOIN - If there's data that you want to access from multiple tables that are connected, you have to join them. JOIN is accompanied by the ON clause, which defines the relationship between two tables, and the order of the columns or tables within the clause does not matter ON A.id = B.id.

WHERE - How you filter. What's the criteria? What do you want to filter out?

GROUP BY - This squishes the data from SELECT, FROM, and WHERE, and groups them together based on a common attribute, usually through a calculation/aggregate.

HAVING - This is essentially a where clause that allows you have an aggregate function in it. For the new column that's created, you can choose if you want to only select values over a certain value etc.

ORDER BY - This orders the results of the query in a specific way, commonly used with ASC or DESC.

LIMIT - Limits the results of the query to a certain number.

/* All Rows & All Columns (Whole Table) */

SELECT *
FROM shelters;

/* All Rows, Specific Columns */

SELECT name, city
FROM shelters;

/* Specific Rows, All Columns */

SELECT *
FROM pets
WHERE adoptable = true AND (age > 8);

/* Specific Rows, Specific Columns */

SELECT name, breed, age
FROM pets
WHERE adoptable = true AND (age > 3);

Case-Sensitive

/* String is an exact match */

SELECT *
FROM teams
WHERE name = 'Colorado Avalanche';

/* String is a similar match */

SELECT *
FROM teams
WHERE name LIKE '%Colorado%';

/* Includes 'C' */

SELECT *
FROM teams
WHERE name LIKE '%C%';

/* Includes 'c' */

SELECT *
FROM teams
WHERE name LIKE '%c%';

/* Includes 'ha' */

SELECT *
FROM teams
WHERE name LIKE '%ha%';

Case-Insensitive (PostgreSQL only)

/* String is an exact match */

SELECT *
FROM teams
WHERE name = 'Colorado Avalanche';

/* String is a similar match */

SELECT *
FROM teams
WHERE name ILIKE '%Colorado%';

/* Includes 'C' */

SELECT *
FROM teams
WHERE name ILIKE '%C%';

/* Includes 'c' */

SELECT *
FROM teams
WHERE name ILIKE '%c%';

/* Includes 'ha' */

SELECT *
FROM teams
WHERE name ILIKE '%ha%';

SELECT *
FROM cd.facilities
WHERE facid = 1 OR facid = 5

SELECT *
FROM cd.facilities
WHERE facid IN (1, 5)

SELECT *
FROM cd.facilities
WHERE facid IN  (
                  SELECT facid
                  FROM cd.facilities
                );

The reason why this works is because it defaults to 00:00, so it needs to be more than or equal to because it needs to capture the records that are after midnight.

In other words, if it were only equal to, then the records would need to match this exactly: 2026-05-08 00:00:00.

SELECT memid, surname, firstname, joindate
FROM cd.members
WHERE joindate >= '2012-09-01';

A JOIN query is what we use to combine data from two or more different tables. It is possible to join many tables together in order to gather information from multiple tables.

/* All Columns, Both Tables */

SELECT *
FROM songs
JOIN artists ON artists.id = songs.artist_id;

/* All Columns, Both Tables (Flipped) */

SELECT artists.*, songs.*
FROM songs
JOIN artists ON artists.id = songs.artist_id;

/* Specific Columns, Both Tables */

SELECT artists.name, songs.title
FROM songs
JOIN artists ON artists.id = songs.artist_id;

/* Which artists have songs with a play count greater than 10000? */

SELECT DISTINCT artists.name
FROM songs
JOIN artists ON artists.id = songs.artist_id
WHERE songs.play_count > 10000
ORDER BY artists.name;

/* Which artists have songs with a length greater than 400? */

SELECT DISTINCT artists.name
FROM songs
JOIN artists ON artists.id = songs.artist_id
WHERE songs.length > 400
ORDER BY artists.name;

/* Which playlists have songs with a length greater than 400? */

SELECT DISTINCT playlists.name
FROM songs
JOIN playlists ON playlist.id = song.id
WHERE songs.length > 400;

Grouping simply takes whatever you have selected and makes unique groups out of it, based on what you specify. If the query doesn't know how to condense each row into a unique one, and then put them back together.

SELECT team_id
FROM players
GROUP BY team_id;

An aggregate function is a function in sequel that will perform some sort of calculation.

/* Minimum */

SELECT min(length)
FROM songs;

/* Maximum */

SELECT max(length)
FROM songs;

/* Count */

SELECT count(*)
FROM songs;

/* Sum */

SELECT sum(play_count)
FROM songs;

/* Average */

SELECT avg(length)
FROM songs;

SELECT artist_id, avg(length)
FROM songs
GROUP BY artist_id;

SELECT team_id, avg(sweater_number)
FROM players
GROUP BY team_id;

SELECT team_id AS team, count(*) AS players_on_team
FROM players
GROUP BY team_id;

SELECT *
FROM players
INNER JOIN teams ON teams.id = players.team_id

SELECT artists.*, avg(length) AS avg_length

FROM songs
INNER JOIN artists ON artists.id = songs.artist_id

GROUP BY artists.id

SELECT merchants.id AS merchant_id, merchants.name AS merchants_name, customers.id AS customer_id, customers.first_name, customers.last_name, count(transactions.result) AS transaction_result

FROM customers
JOIN invoices ON invoices.customer_id = customers.id
JOIN transactions ON transactions.invoice_id = invoices.id
JOIN invoice_items ON invoice_items.invoice_id = invoices.id
JOIN items ON items.id = invoice_items.item_id
JOIN merchants ON merchants.id = items.merchant_id

WHERE transactions.result = '1' AND merchants.id = '1'

GROUP BY merchants.id, customers.id

ORDER BY merchants.id, transaction_result DESC
LIMIT 5;

INSERT INTO items (name, revenue, season_id)
VALUES  ('tacos', 700, 1);

SELECT * FROM items WHERE name = 'lobster mac n cheese';

UPDATE items
SET name = 'updated tacos', revenue = 1000
WHERE id = 15;

DELETE FROM items WHERE name = 'burger';

ALTER TABLE public.login_attempts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Service role only" -- A name for the policy
ON public.login_attempts -- The target table
FOR ALL -- Policy applies to all operations SELECT, INSERT, UPDATE, DELETE
USING (false) -- No rows are visible
WITH CHECK (true); -- Controls which rows are allowed to be inserted or updated

pg_dump -U [db user/role] -d [db name] > [backup file name]
pg_dump -U grantdavis303 -d sticky_notes > cool_backup.sql

Create a new, empty database

createdb -T template0 dbname
createdb -T template1 sticky_notes

Load data into the new, empty database

psql dbname < infile
psql sticky_notes < cool__backup.sql