Creating Databases and Tables

Datatypes

• Boolean (True or False)
• Character (char, varchar, and text)
• Numeric (integer and floating-point number)
• Temporal (date, time, timestamp, and interval)
• UUID (universally-unique identifiers)
• Array (stores array of strings, numbers, …)
• JSON
• Hstore key-value pairs

• Special types such as network address and geometric data

• documentation

Primary and foreign keys

• Primary key: unique identifier (PK)
• Foreign key: refers to unique identifier of a different table

Constraints

• NOT NULL - ensures that a columns cannot have a null value
• UNIQUE - ensures that all values in a columns are different
• PRIMARY KEY - uniquely identifies each row/record in a database table
• FOREIGN KEY - constrains data based on columns in other tables
• CHECK - ensures that all values in a columns satisfy certain conditions
• EXCLUSION - ensures that if any two rows are compared on the specified columns or expression using the specified operator, not all of these comparisons will return TRUE.

Table constraints

• CHECK(condition) - to check a condition when inserting or updating data
• REFERENCES - to constrain the value stored in the column that must exist in a column in another table
• UNIQUE(column_list) - forces the values stored in the columns listed inside the parentheses to be unique
• PRIMARY KEY(column_list) - allows you to define the primary key that consists of 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;

• example:

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

• SERIAL:
  • in PostgreSQL, a sequence is a special kind of database object that generates a sequence of integers.
  • a sequence is often used as the primary key column in a table.
  • SERIAL will create a sequence object and set the next value generated by the sequence as the default value for the column.
  • perfect for a primery key, because it logs unique integer entries upon insertion.
  • if a row is later removed, the column with the SERIAL data type will not adjust, marking the fact that a row was removed.

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_date TIMESTAMP
)

INSERT:

• Allows you to add rows to table.

• Adding rows to the table manually:

INSERT INTO table (column1, column2, ...)
VALUES
(value1, value2, ...),
(value1, value2, ...),...;

• Inserting values from another table:

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

INSERT into account(username,password,email,created_on)
VALUES
('Jose','password','jose@gmail.com',CURRENT_TIMESTAMP)

INSERT INTO job(job_name)
VALUES
('Astronaut')

INSERT INTO job(job_name)
VALUES
('President')

INSERT INTO account_job(user_id,job_id,hire_date)
VALUES
(1,1,CURRENT_TIMESTAMP)

UPDATE:

• Changing of values of the columns in a table.

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

• Example:

UPDATE account
SET last_login = CURRENT_TIMESTAMP
WHERE last_login IS NULL;

• Set based on another column:

UPDATE account
SET last_login = created_on

• Use another table’s values (aka “Update join”):

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

• Return affected rows:

UPDATE account
SET last_login = created_on
RETURNING account_id, last_login

DELETE:

• Removing rows from a table:

DELETE FROM Table
WHERE row_id = 1

• Deleting rows based on their presence on other tables:

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

• Can add a RETURNING statement at the end to see which rows were deleted.

ALTER:

• Documentation

ALTER TABLE table_name action

• Example:

ALTER TABLE table_name
ADD COLUMN new_col TYPE

ALTER TABLE table_name
DROP COLUMN col_name

ALTER TABLE table_name
ALTER COLUMN col_name
SET DEFAULT value

ALTER TABLE table_name
ALTER COLUMN col_name
DROP DEFAULT

ALTER TABLE table_name
ALTER COLUMN col_name
SET NOT NULL

ALTER TABLE table_name
ALTER COLUMN col_name
ADD CONSTRAINT constraint_name

Example from lectures:

CREATE TABLE information(
	info_id SERIAL PRIMARY KEY,
	title VARCHAR(500) NOT NULL,
	person VARCHAR(50) NOT NULL UNIQUE
)

ALTER TABLE information
RENAME TO new_info;

ALTER TABLE new_info
RENAME COLUMN person TO people;

ALTER TABLE new_info
ALTER COLUMN people DROP NOT NULL

DROP:

• Completely remove columns from a table. This will remove all indices and constraints involving the column. However, it will not remove columns used in views, triggers, or stored procedures without the additional CASCADE clause.

ALTER TABLE table_name
DROP COLUMN col_name

ALTER TABLE table_name
DROP COLUMN IF EXISTS col_name

• drop multiple columns:

ALTER TABLE table_name
DROP COLUMN col1,
DROP COLUMN col2

CHECK:

• Allows us to create more customized constraints that adhere to a certain condition, such as making sure all inserted integer values fall below a certain threshold.

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

• Example from lecture:

CREATE TABLE employees(
	emp_id SERIAL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	birthdate DATE CHECK (birthdate > '1900-01-01'),
	hire_date DATE CHECK (hire_date > birthdate),
	salary INTEGER CHECK (salary >= 0)
)

INSERT INTO employees(
	first_name, last_name, birthdate, hire_date,salary
)
VALUES
('Rey','Cruz-Torres','1990-03-03','2010-01-01',-100)

• this issues a failure because there’s a constraint that salary must be non-negative.