POSTGRESQL

Relational DB (like a spreadsheet)

SQL (Structured Query Language)

TABLE

• columns (named and places constraints on the rows)
• rows (entities)

1. CRUD Operations:

• creating a table

CREATE TABLE profile (
  id SERIAL primary key, -- serial means auto increments, primary key is the unique one in the table to distinguish rows
  name varchar(100), -- varchar size restricts the size of the data
  email varchar(255),
  age int, 
  password text -- password is hashed and can be long
);

CREATE TABLE "user"( -- user is a keyword, so have to wrap in quotes to distinguish
  id SERIAL primary key, -- serial means auto increments
  name varchar(100), -- varchar size restricts the size of the data
  email varchar(255),
  age int, 
  password text -- password is hashed and can be long
);

we don't want to name using plurals (bag of candies but not bags of candies)

• inserting into table

INSERT INTO "user"(email, age, name) VALUES ('abc@xyz.com', 18, 'blahblah');
INSERT INTO "user"(email, age, name) VALUES ('abc@xyz.com', 18, 'blahblah');

double quotes for tables, single quotes for string, text based values

• Selecting data from the table

SELECT * FROM "user";                  -- gives all columns
SELECT id, name, email FROM "user";   -- filters specific cols
SELECT * FROM "user" WHERE age > 16;  -- filters rows

• update

UPDATE "user" SET age = 20 WHERE name = 'blahblah';

• Delete

DELETE FROM "user" WHERE id = 2; -- deletes the row

2. TABLE Relations

Tables have relations with other tables (like user to post).
This is how we relate an entity in one table to another: we use a foreign key to reference entities in other tables.

Entity relationship diagram

• 1 - 1 relationship:
  user -- avatar_id ---referencing a row in avatar table
  avatar -- user_id -- referencing a user in user table

• 1 - many:
  Many references one (in post table, post can refer user_id).
  (if we try to refer post_id from user table, it means it can have only 1 post - so incorrect)

• many - many:
  combination of 1 to many and many to 1 tables
  1 --> many (join table) many <-- 1

user ---> class <--- instructor

A class can have multiple users and multiple instructors.

CREATE TABLE post(
  id SERIAL PRIMARY KEY,
  content TEXT,
  likes INT,
  user_id INT,
  CONSTRAINT fk_user
    FOREIGN KEY(user_id)
      REFERENCES "user"(id)
);

INSERT INTO post(content, likes, user_id) VALUES ('hello', 112, 1);

Now get the combined data from the 2 tables connected using foreign keys:

SELECT * FROM "user" JOIN post ON post.user_id = "user".id;

can update the column names using AS and get specific columns

SELECT "user".id, "user".name, post.content AS title, post.likes 
  FROM "user" JOIN post 
    ON post.user_id = "user".id;

3. Advanced

• complex joins
• schema design
• triggers
• postgres functions
• complex constraints