Link Search Menu Expand Document

SQL Database Cheat Sheet

Table of contents

Data Types

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 an array of strings, numbers, etc
JSON 
Hstore key-value pair 
Special types such as network address and geometric data 

Primary Keys and Foreign Keys

  • Primary Keys » Integer based and Unique Identifier
  • Foreign keys » Referring table or child table, the column title also appear in other tables.

Constraints

  • Database > Schemas > Tables > Constraints
NOT NULL constraint
    # ensure that a column cannot have NULL value 
UNIQUE constraint
    # ensure that all values in a column are different 
CHECK constraint
    # ensure that all values in a column satisfy certain conditions 
EXCLUSION constraint 
    # ensure that if any 2 rows are compared on the specified column or expression using the specified operator, not all of these comparisons will return TRUE

Create Table

  • General Syntax - use comma , to separate columns
CREATE TABLE [table_name] (
    column_name TYPE column_constraint, 
    column_name TYPE column_constraint, 
    table_constraint table_constraint 
) INHERITS existing_table_name; 
  • Common Simple Syntax - Use serials sequence object, as primary key
CREATE TABLE [table_name] (
    [column_name] TYPE column_constraint, 
    [column_name] TYPE column_constraint, 
);
  • Sample Table 1
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
)
  • Sample Table 2
CREATE TABLE job (
    job_id SERIAL PRIMARY KEY, 
    job_name VARCHAR(200) UNIQUE NOT NULL 
)
  • Sample Table 3
CREATE TABLE account_job (
    user_id INTEGER REFERENCES account(user_id), 
    job_id INTEGER REFERENCES job(job_id), 
    hire_date TIMESTAMP
)

Import CSV

  • Step 1 - create table, make sure you don’t have comma , in the last row.
CREATE TABLE public.table_name (
    data_id SERIAL PRIMARY KEY, 
    col_a VARCHAR(100),
    col_b VARCHAR(100),
    inside_x double precision, 
    inside_y double precision,
    "2000" INTEGER
);
  • Step 2 - two methods
    1. use below code
    2. use PG Gui for import/export
COPY table_name FROM '/Users/name/Downloads/example.csv' WITH (FORMAT CSV, HEADER);

COPY table_name(col_name_1, col_name_2, col_name_3, col_name_4)
FROM 'path/to/file.csv'
DELIMITER ','
CSV HEADER;

Insert Value

  • The insert row value must match up for the table SERIAL col do not need to provided a value
INSERT INTO table_name(col_1, col_2, ...)
VALUES 
    (value_1, value_2, ...),
    (value_1, value_2, ...), ...;

INSERT INTO table_name(col_1, col_2, ...) 
SELECT col_1, col_2, ...
FROM another_table
WHERE condition; 

Update Table Syntax

UPDATE table_name
SET col_1 = value_1,
    col_2 = value_2, ...
WHERE condition; 

Delete

DELETE FROM table_name
WHERE col_name_1 = 1

DELETE FROM table_a
USING table_b
WHERE table_a.id = table_b.id

DELETE FROM table_name

Alter

ALTER TABLE table_name action 

ALTER TABLE table_name
ADD COLUMN new_col TYPE

ALTER TABLE table_name
DROP COLUMN new_col 

ALTER TABLE table_name
ALTER COLUMN col_name_1

SET DEFAULT value 
SET NOT NULL 
DROP NOT NULL 
ADD CONSTRAINT constraint_name 

Drop Table

DROP TABLE table_name

ALTER TABLE table_name
DROP COLUMN col_name_1 CASCADE

ALTER TABLE table_name
DROP COLUMN IF EXSITS col_name_1

ALTER TABLE table_name
DROP COLUMN col_1,
DROP COLUMN col_2 

Check Constraint

CREATE TABLE example (
    ex_id SERIAL PRIMARY KEY, 
    age SMALLINT CHECK (age > 21), 
    parent_age SMALLINT CHECK (parent_age > age)
    /* SMALLINT -> Small Integer */
);