Just giving credit where credit is due: these are my notes from A udemy course I am taking

Building a table/DB is one of the more important parts: to run a query successfully you need to store the data in a way it can be searched well. One thing you really don’t want to do is have duplicate data. If you have dup data at some point it will get updated in once spot, but not in the other. You also need to choose the correct data type per column. Figuring out what the use-cases going forward will be can impact how you design the database.

Data Types

Full list of datatypes here: https://www.postgresql.org/docs/current/datatype.html

BOOLEAN: True/False

Character: CHAR, VARCHAR, TEXT

Numeric: Intger, floating point number

Temporal: Date, time, timestamp, interval

UUID: Unique ID

Array: Na array of strings, numbers, etc

JSON

SERIAL: Often the primary key: SERIAL just makes the column and for every row it just upps the number to the next int in the row

Primary Key

The primary key is a column or group of columns used to uniqely ID a row

Foreign Key

A key/group of columns that ID a row in another table: customer_id in a phone numbers table ties that phone number to a customer in the customers table.

Constraints

Rules enforced on data columns in a table. They stop you from putting bad data into the DB, thus helping to ensure the queries work well.

There are Column constraints where what’s being put into the column must meet certian conditions

There are Table constraints that are applied to the entire table

Common Constraitns

• NOT NULL: Ensure a column can’t have a NULL value
• UNIQUE: All values in a column are diffrent
• PRIMARY KEY: IDs each row in the DB as unique
• FOREIGN KEY: Makes sure this column matches data in another table
• CHECK: Ensures all values in a column match a certian condition
• REFERENCES: The value stored in the column must exit in a column in another table
• UNIQUE (column_list): Focres the values in the columns listed to be unique
• PRIMARY KEY (column_list): Makes many columns into the primary key

CREATE

Used to create a table

General random table to create

CREATE TABLE table_name(
column_name [BOOLIAN|VARCHAR|JSON|ETC] column_constraint ,
customer_id SERIAL PRIMARY KEY,
age SMALLINT NOT NULL
height SMALLINT);

Next lets make interlocking tables that refer to one another

create table account(
user_id SERIAL PRIMARY Key,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(50) NOT NULL,
email VARCHAR(250) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);

create table JOB(
job_id SERIAL PRIMARY Key,
job_name VARCHAR(200) UNIQUE NOT NULL

);

CREATE TABLE account_job(
user_id INTEGER REFERENCES account(user_id),
job_id INTEGER REFERENCES job (job_id),
hire_data TIMESTAMP
);

INSERT

Stick rows into a table: the item in values lines up with the column in the INSERT INTO: So in the 1st example below username matches up with Bob, and Bob1 as they are both in the 1st position in the “list”

INSERT INTO account (username, password, email, created_on)
VALUES (‘Bob’, ‘ted’, ‘[email protected]’, CURRENT_TIMESTAMP),
(‘Bob1’, ‘ted1’, ‘[email protected]’, CURRENT_TIMESTAMP);

INSERT into job(job_name) values (‘President’), (‘Truck Driver’);

INSERT INTO account_job (user_id, job_id, hire_data)VALUES(2,2,CURRENT_TIMESTAMP);

UPDATE

Changes the value in the column:

UPDATE table_name SET column_name_1= ‘BOB’, column_name_1= ‘TED, where id=5;

Without the where it updates everything

What’s known as an UPDATE JOIN

UPDATE TableA SET original_col = TableB.new_col FROM tableB WHERE tableA.id = TableB.id

DELETE

Remove a row

DELETE FROM table_a WHERE row_id = 1;

DELETE FROM table_a USING tableB WHERE tableA.id = tableB.id;

Remove everything from a table with

DELETE FROM table_a

Get back the rows that were removed with RETURNING

DELETE FROM table_a WHERE row_id = 1 RETURNING cost, row_id, name;

ALTER

Changes the existing table structure: add/remove/rename columns, change column data type, set default values for column, add CHECK constraints, rename table, etc

ALTER TABLE table_name ADD COLUMN new_column_name TYPE

ALTER TABLE table_name DROP COLUMN column_name

ALTER TABLE table_name COLUMN column_name SET DEFAULT default_value

ALTER TABLE table_name COLUMN column_name SET NOT NULL

ALTER TABLE table_name COLUMN column_name DROP NOT NULL (Makes it allow a NULL value)

ALTER TABLE old_table_name RENAME TO new_table_name

DROP

Remove a or column

DROP TABLE table_name;

ALTER TABLE table_name DROP COLUMN column_name

Won’t remove columns used in views, triggers, stored procedures, etc without a CASCADE clause

CHECK Constraint

Make custom constraints

CREATE TABLE table_name(ex_id SERIAL PRIMARY KEY age SMALLINT CHECK (age>21), parent_age SMALLINT CHECK (parent_age>age));