Admin

Roles

CREATE ROLE username LOGIN PASSWORD 'password' SUPERUSER VALID UNTIL 'infinity; // superuser
CREATE ROLE username LOGIN PASSWORD 'password' CREATEDB VALID UNTIL 'infinity'; // login user that can create databases
ALTER ROLE username WITH LOGIN;

SELECT User FROM pg_user;

Group roles are generally roles that have no login rights but have other roles as members.

GRANT ALL PRIVILEGES ON DATABASE [database] TO [role];
GRANT ALL PRIVILEGES ON TABLE [table] TO [role];

Creating and altering databases and tables

CREATE DATABASE [database] [TEMPLATE template];
DROP DATABASE [database]; // delete database
DROP TABLE [table]; // delete table
CREATE SCHEMA [schema];

CREATE TABLE [tablename] (
	column_name_2 datatype,
	column_name_1 datatype
);
CREATE TABLE users (
	id serial PRIMARY KEY,
	name varchar(25),
	email citext UNIQUE
);
CREATE TABLE comments (
	id serial PRIMARY KEY,
	user_id int REFERENCES users(id),
	oid varchar(64) NOT NULL,
	username varchar(16) NOT NULL,
	text varchar(10000) NOT NULL,
	created_at timestamp default now()
);

ALTER TABLE [tablename];
ADD COLUMN [column name] [datatype];
ALTER TABLE [tablename] DROP [column]; // drop column in existing table
ALTER TABLE [tablename] ALTER COLUMN [column] TYPE varchar(64); // change type of column

Hashing password

Best practice is to encrypt the password in the app layer (for example using bcrypt) rather than the database layer, because the password will be sent between the layers.
CREATE EXTENSION pgcrypto;
INSERT INTO Users (password) values crypt(:password, gen_salt('bf', 8));

Working with data

Retrieve data: SELECT

SELECT * // column/s  
FROM pg_available_extensions // table  
WHERE comment LIKE '%string%' OR installed_version IS NOT NULL // filter
ORDER BY name; // sort data by columns. Defaults to Ascending, otherwise DESC keyword for descending

SELECT [column], [column] FROM [table];
SELECT id, title, genre FROM movies;
SELECT * FROM movies; // selects all columns from movies
SELECT title FROM movies WHERE id = 2; // filter for id = 2
SELECT * FROM movies WHERE title = 'The Kid'; // filter for string title, single quotes for strings

WHERE comparison operators: =, <, >, <=, >=, <> / !=
AND OR // works with WHERE

Retrieve information on a table

\d table_name;
SELECT [column name], [data type] FROM information_schema.columns WHERE table_name = 'table name';

Insert data

INSERT INTO [table name] ([column/s])  
VALUES ([data values for each column]);
INSERT INTO moves (id, title, genre, duration) // ([column/s]) can be skipped if adding into all columns  
VALUES (5, 'The Circus', 'Comedy', 71);

NULL is used to represent a field with no data.

Update data

UPDATE [table]
SET [column] = [data value], [column] = [data value] // comma to separate multiple updates
WHERE [filer]; // optional

Delete data

DELETE FROM [table]
WHERE [FILTER]; // optional

Common Aggregate Functions

Can have more than one in a SELECT statement followed by comma.  Can also group by.

SELECT [column], aggregate_function([column])
FROM [table]
WHERE
GROUP BY [column]
HAVING aggregate_function([column]) [operator] [value];

count - number of rows

SELECT count([column])
FROM [table];

sum - sum of values - numbers only

SELECT sum([column])
FROM [table];

avg - calculated average value - numbers only

SELECT avg([column])
FROM [table];

max - largest value - numbers only

SELECT max([column])
FROM [table];

min - smallest value - numbers only

SELECT min([column])
FROM [table];

Constraints - used when creating tables

Constraints prevent bad or unwanted data entering tables.  Multiple constraints per column allowed. Modify CREATE TABLE syntax to create a column constraint:

NOT NULL
UNIQUE
PRIMARY KEY (NOT NULL && UNIQUE)

Custom constraint names also available (creates a table constraints). Cannot create a custom NOT NULL table constraint.  Multiple unique columns allowed, separate with ,

CONSTRAINT constraint_name UNIQUE ([column])

Primary Key

A primary key is a column in a table which must have a unique value.  Such as an id.

Foreign Key

A Foreign Key is a column in one table that references the primary key column of another table.
Naming convention: singularise_id;
eg. movie_id would refer to id in movies table

Foreign Key Constraint prevents inserting incorrectly into foreign key column.
movie_id int REFERENCES movies(id) // movies - defaults to primary key
FOREIGN KEY ([column]) REFERENCES [table] // defaults to primary key

Check constraint

Validate the value input into the column
[column] int CHECK ([column] ] 0)

Normalisation

Normalisation is the process of reducing duplication in database tables.

Rules of normalisation:
1. Tables must not contain repeating groups of data in 1 column
2. Tables must not contain redundancy (unnecessary repeating information)

In the example, multiple genres allowed by form.  Rather than include the genre in the movies table, solution creates a  genre table, and a join table.

Join table naming convention: Table1_Table2

Two columns in join table:
table1_id : references the primary key of the table1
table2_id: references the primary key of the table2
Both columns are foreign keys because they reference primary keys.

Relationships

One-to-One
One-to-Many
Many-to-Many (use a join table)

Joins

[select_list] example: Movies.title, Reviews.review would just return those 2 columns.

Inner Join on Multiple Tables (Using the many-to-many join table)

SELECT Movies.title, Genres.name
From Movies
INNER JOIN Movies_Genres
ON Movies.id = Movies_Genres.movie_id
INNER JOIN Genres
ON Movies_Genres.genre_id = Genres.id
WHERE Movies.title = "Peter Pan";

Aliases

Aliases in the SELECT allow for more understandable headers.
SELECT Movies.title AS films, Genres.name AS genres // can omit the AS

Table aliases allow us to add add an alias in the FROM or JOIN query: Movies m, and then can use m. wherever else in the query.

Subqueries

Query in parentheses is a subquery:

SELECT SUM(sales)
FROM Movies
WHERE id IN
(SELECT movie_id
FROM Promotions
WHERE category = 'Non-cash');

Can sometimes write subqueries as joins.  Joins more performant, potentially less easier to read.  Can filter for IN (filter rows with matching id) or NOT IN (filter rows without matching id).

Custom Functions

Updated_at column

This function simply sets any column named 'modified' to the current timestamp for each row passed to it by the trigger. If you use the same column name consistently you only have to do this step once.

CREATE OR REPLACE FUNCTION update_updated_at_column()    
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;    
END;
$$ language 'plpgsql';

Now, you just have to create your trigger like so:
CREATE TRIGGER user_updated_at_time BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();