Data Types

See the documentation:

https://www.postgresql.org/docs/13/datatype.html

Main Data Types

Type Value
Boolean True or False
Character char, varchar, and text
Numeric integer and floating-point number
Temporal date, time, timestamp, and interval

Not as Common

  • UUID - Universally Unique Identifiers
  • Array - Stores an array of strings, numbers, etc.
  • JSON
  • Hstore key-value pair
  • Special types such as network address and geometric data

Primary and Foreign Keys

A primary key is a column or group of columns that can uniquely identify a row.

A foreign key is a column or group of columns that can uniquely identify a row in another table.

Here you can see in the constraints that payment_id is the primary key and customer_id, rental_id, and staff_id are foreign keys.

Constraints

  • Rules enforced on data columns
  • Prevent invalid data from being entered

Most Common Column Constraints:

  • NOT NULL- column cannot have a NULL value
  • UNIQUE - all values in a column must be different
  • CHECK - all values in a column satisfy certain conditions
  • EXCLUSION - when comparing any two rows using a specified operator, not all comparisons will be TRUE
  • Primary and Foreign Keys

Most Common Table Constraints:

  • CHECK(condition) - check a condition when inserting or updating data
  • REFERENCES - constrain the value in a column when that column also exists in another table
  • UNIQUE(column_list) - UNIQUE on multiple columns
  • PRIMARY KEY(column_list) - Primary key on multiple columns

CREATE Table

GENERAL SYNTAX:

CREATE TABLE table_name (
column_name TYPE column_constraint,
column_name TYPE column_constraint,
table _constraint table_constraint
) INHERITS existing_table_name;

TYPE = SERIAL

  • It will create a sequence object. Each new entry will be assigned the next value in the sequence.
  • If the row is removed, the assigned values will not adjust.
  • This is very useful for the Primary Key.

EXAMPLE SYNTAX:

CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
age SMALLINT NOT NULL,
);



INSERT

INSERT allows you to add rows to a table.

GENERAL SYNTAX:

INSERT INTO table (column1, column2, …)
VALUES
(value 1, value 2,…),
(value 1, value 2,…),…;

GENERAL SYNTAX - INSERT FROM ANOTHER TABLE:

INSERT INTO table (column1, column2, …)
SELECT column1, column2,…
FROM another_table
WHERE condition;




UPDATE

UPDATE allows you to change the values of a column.

GENERAL SYNTAX:

UPDATE table
SET column1 =value1,
column2 = value2,…
WHERE
condition;

EXAMPLE:

UPDATE account
SET last_login = CURRENT_TIMESTAMP,
WHERE last_login IS NULL;

UPDATE TableA
SET original_column = TableB.new_column,
FROM TableB
WHERE TableA.id = TableB.id




DELETE

DELETE will remove rows from a table.

GENERAL SYNTAX:

DELETE FROM table
WHERE row_id = 1

DELETE FROM TableA
USING TableB
WHERE TableA.id = TableB.id

To delete all rows:
DELETE FROM Table


ALTER table

ALTER allows you to change an existing table structure

  • Adding, dropping, or renaming columns
  • Changing a column’s data type
  • Set Default values for a column
  • Add CHECK constraints
  • Rename table

https://www.postgresql.org/docs/13/sql-altertable.html

GENERAL SYNTAX:

ALTER TABLE table_name
action

EXAMPLE:

ALTER TABLE table_name
ADD COLUMN new_col TYPE




DROP table

DROP completely removes a column from a table

  • Will automatically remove all of its indexes and constraints.
  • Will not remove columns used in views, triggers, or stored procedures without the additional CASCADE clause.

GENERAL SYNTAX:

ALTER TABLE table_name
DROP COLUMN column_name

ALTER TABLE table_name
DROP COLUMN column_name CASCADE

ALTER TABLE table_name
DROP COLUMN IF EXISTS column_name


CHECK Constraint

CHECK will add constraints that adhere to a condition.

GENERAL SYNTAX:

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