Welcome to my PostgreSQL notebook during my backend learning progress.

By following along, you could learn some solid foundational understanding of PostgreSQL.

Challenges of Postgres

• Writing EFFICIENT QUERIES to retrieve information

• Designing database schema or structure.

• Understanding when to use advanced features.

• Managing the database in a production environment.

Basic Terminology

• Inside a database, we can have multiple tables.

• A table stores a list of records, like a list of to-do items, a list of photos, a list of cities, and a list of food ingredients,…

• Less formal definition: think of a database like a Folder, inside the folder we can have multiple spreadsheets (tables)

Database Design Process

• What kind of THING are we storing?

• What properties does this thing have?

• What type of data does each of those properties contain?

Let’s CREATE our first table

CREATE TABLE cities (
      name VARCHAR(50),
      country VARCHAR(50),
      population INTEGER, 
      area INTEGER
  );

Few things we need to understand about the syntax here:

1. Keywords CREATE TABLE , to tell the DB that we want to do something. Always written out in CAPITAL letters.

2. Identifiers cities - tell the DB what thing we want to act on—always written in lowercase letters.

3. Next is all the content inside of parenthesis () , we defined 4 filed name, country,… , in total, we say to the DB we want 4 columns with the name, and type of data we want to store inside of them.

Column Data types in the example

• VARCHAR(50) variable length character. Text. If we put a string longer than 50 characters, we’ll get an error.

• INTEGER Number without a decimal.

INSERT data into a table

INSERT INTO cities (name, country, population, area)
VALUES ('New York', 'USA', 1213112, 28223);

The syntax seems quite straightforward.

We must insert value in the correct order, with the right value type.

We can also insert multiple rows in a single statement in one go.

INSERT INTO cities (name, country, population, area)
VALUES 
    ('Denver', 'USA', 21111, 2323),
    ('New York', 'USA', 1213112, 28223),
    ('Tokyo', 'Japan', 201234, 5678);

Retrieving Data with SELECT

SELECT * FROM cities;

The * simply say: “Hey, please give me every records inside this table”

The result we get:

Select a specific columns from our table

SELECT name, population FROM cities;

SQL is not just about pulling raw data out of a table.

We can write SQL to transform or process data before we receive it.

SELECT name, population / area as population_density FROM cities;

// RESULT: 
name    population_density
Tokyo        14
Denver        9
New York    42

String Operators and Functions

| || | Join two strings | | --- | --- | | CONCAT() | Join two strings | | LOWER() | Gives a lowercase string | | LENGTH() | Gives the total number of characters in a string | | UPPER() | Gives an upper case string |

Examples:

SELECT
  CONCAT(name, ', ', country) as location
FROM
  cities;

SELECT
  name || ', ' || country as location
FROM
  cities;

Both of the above queries give us the same result

We want to join the name with a , and with country and split the results in a table with a column name location

SELECT
  UPPER(CONCAT(name, ',', country)) as location
FROM
  cities;

---

Filter Records

Filter rows with WHERE keyword

SELECT name, area FROM cities WHERE area > 5000;

Now we don’t get back the entire records (rows) of cities anymore.

We narrow down the search by giving our query the condition WHERE area > 5000

We only get back

name        area
Tokyo        8223
New York    28223

The order of query execution

Take a look at the query below

SELECT name, area    FROM cities     WHERE area > 5000;
-- THIRD --          -- FIRST --     ---- SECOND ----

Here is the actual order in which Postgres executed.

First, it takes a look at a DATA SOURCE.

Second, apply the FILTER criteria. For each row, I ONLY want to consider the rows that have an area greater than 5000.

Lastly, for all the remaining columns, I just want to select some number of columns. In the example they are name, area

Some comparison math operators

=, >, < , >= , <= are basic ones.

IN - is the value present in a list?

<> Are the values NOT EQUAL?

!= Are the values NOT equal? (same as the above)

BETWEEN Is the value between two other ones?

NOT IN is the value NOT present in a list?

Updating and Deleting Rows

Update syntax

UPDATE cities
SET population = 3920000
WHERE name = 'Tokyo';

DELETE syntax

DELETE FROM cities 
WHERE name = 'Tokyo';

---

Working with Tables 🧩

What Tables should we make?

• Common features (like authentication, comments, etc) are frequently built with conventional table names and columns.

• What type of resources exist in your app? Create a separate table for each of these features.

• Features that seem to indicate a relationship or ownership between two types of resources need to be reflected in our table design.

One-to-Many Relationships

To better illustrate, let’s imagine we have to design a database for a simple social media application for posting photos - Like Instagram.

Where each User can post their own photos.

👉 ONE user HAS MANY photos.

Many-to-One Relationships

👉 Many different photos belong to ONE single user.

👉 Many-to-one or One-to-Many is exactly the opposite of each other, it depends upon the perspective you’re looking at.

Similarly, when a user posts a Photo , their friends can also comment on that.

🔵 A photo, can have MANY comments

🔵 Each comment has one photo Or MANY comments belong to ONE photo.

One-to-One Relationship

Here are a few examples:

ONE COMPANY - Have ONE CEO.

ONE PERSON - Have ONE identity card.

ONE BOATS - Have ONE Captain.

Many-to-Many Relationship

Tasks can belong to multiple engineers, and engineers can have multiple tasks.

Many movies can have many actors/actresses, and actors can belong to many movies.

Students can have multiple classes, and classes can have multiple students.

Ingredients can belong to many recipes, and recipes can have many ingredients.

Primary Keys and Foreign Keys

Example photos table

• Primary Key: Uniquely identifies this record in this table - id

• Foreign Key: Identifies a record usually in ANOTHER table - that this row is associated with. - user_id

Example users table

A user can have multiple photos.

In the photos table, we want to somewhere modeling that relationship.

By having a foreign key user_id attached to each photo record, we can reference the id of the user who owns these photos.

Some distinctions

• Primary Keys:

  • Each row in every table has ONE primary key.

  • No other row in the SAME table can have the SAME value.

  • 99% of the time called id

  • Either an integer or a UUID

  • Will NEVER CHANGE

• Foreign Keys:

  • Rows (records) ONLY HAVE this IF they BELONG TO another record.

  • Many rows in the SAME table can have the SAME foreign key

  • The name varies, usually called something like xyz_id

  • Exactly equal to the value of the referenced row.

  • It will change if the relationship changes.

Auto-generated ID’s

CREATE TABLE users ( 
id SERIAL PRIMARY KEY, 
username VARCHAR (50) 
);

SERIAL tells PostgreSQL to automatically generate the values in the id column for us.

PRIMARY KEY will adds on some special performance benefits to looking up records inside the users table, when we try to looking up by id

Next, let’s insert data into the table

INSERT INTO users(username)
VALUES
('steve92'),
('jen99'),
('joy32');

Let’s see, we just need to insert the value for the username.

The id will automatically generated.

Create Foreign Key Columns

We created the users table.

Let’s say, the user can have multiple photos.

Now we need to create photos table

CREATE TABLE photos(
      id SERIAL PRIMARY KEY,
      url VARCHAR (200),
     user_id INTEGER REFERENCES users(id)
  );

Each photo will belong to a user.

The user_id here is a so-called Foreign key column

This column references the user's primary key in the users table.

INSERT INTO photos(url, user_id)
VALUES 
    ('http://photo-1.jpg', 3);

Here, by inserting a photo url and user_id

We represent a relationship between the photo and the user.

User id=3 own this photo.

What are the main benefits of this foreign key?

Running queries on associated data

Let’s say we want to find all photos created by a user with ID 3

Here is what we can do

SELECT * FROM photos WHERE user_id=3;

If we want to list all photos with details about the associated user for each photo

SELECT * FROM photos
JOIN users ON users.id = photos.user_id;

Foreign Key Constraints

There might be the case we tried to delete a user, but all photo that associated with this user are still existed.

Here’re some delete option we should know about in this case

1. ON DELETE RESTRICT (by default) → will THROW an error

2. ON DELETE NO ACTION → Throw an error

3. ON DELETE CASCADE → If you delete the user, it will delete all the photo that associated with this user_id too

4. ON DELETE SET NULL → set the user_id of the photo to NULL

5. ON DELETE SET DEFAULT → set the user_id of the photo to a default value if one is provide