PostgreSQL cheatsheet

Intro

• Login to postgres user account as superuser: sudo -iu postgres
• Access the prompt with psql.
• You can do it all in one step with sudo -u postgres psql.
• Connect to a different database with psql -d <database name>.

psql commands

Creating new Role

sudo -iu postgres

createuser --interactive --pwprompt

Changing password

ALTER USER user_name
WITH PASSWORD 'new_password';

Databases

Creating

sudo -iu postgres

createdb rkma

Dropping

sudo -iu postgres

dropdb rkma

Or

sudo -u postgres psql

DROP DATABASE IF EXISTS <name>;

Tables

Creating

CREATE TABLE IF NOT EXISTS <table> (
    <column> <data_type> <TableConstraint> DEFAULT <default_value>,
    <another_column> <data_type> <TableConstraint> DEFAULT <default_value>,
    foreign key (<column>) references <table>(<column>)
)

Examples

CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER,
    length_minutes INTEGER
);

create table master_plan(
    id serial primary key,
    the_date date,
    title varchar(100),
    description text
);

Data types

List of data types

Constraints

See: Constraints

Dropping

DROP TABLE IF EXISTS <table_name>;

Renaming table

ALTER TABLE <table_name> RENAME TO <new_table_name>;

See: ALTER TABLE

Altering tables

Adding columns

ALTER TABLE <table name>
ADD <column_name> <data_type> <optional_table_constraint>
    DEFAULT <default_value>;

See: ALTER TABLE

Removing columns

ALTER TABLE <table_name> DROP <column_name>;

Renaming columns

ALTER TABLE table
RENAME COLUMN <column_name> TO <new_column_name>;

Foreign Key

ALTER TABLE <table_name>
ADD FOREIGN KEY (<column id>) REFERENCES lojas(id);

Index

CREATE INDEX <index name> ON <table>(<column);
ALTER TABLE <table name> DROP INDEX <index name>;

Enum

CREATE TYPE <enum name> AS enum(<enum1>, <enum2>);

Values

Insert

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

Update

UPDATE table
SET column1 = new_value1, column2 = new_value2
WHERE <condition>;

Do not forget the where condition!

Delete

DELETE FROM <table name>
WHERE <condition>;

Do not forget the where condition!

Queries

Queries run in this order:

• FROM + JOIN
• WHERE
• GROUP BY
• HAVING
• SELECT + DISTINCT (window functions)
• ORDER BY
• LIMIT

GROUP BY is executed after the WHERE clause. To filter results after grouping, use HAVING.

The HAVING clause constraints are written the same way as the WHERE clause constraints, and are applied to the grouped rows.

SELECT DISTINCT column, AGG_FUNC(column_or_expression)
FROM mytable
JOIN another_table ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT n OFFSET n;

Filtering

SELECT <column_name>
FROM <table_name>
WHERE condition1 AND condition2 OR condition3

Filter operators

When working with text data the following operators are supported:

Aggregate functions

See: Aggregate Functions

Sorting

SELECT DISTINCT column
FROM table
WHERE condition
ORDER BY column ASC/DESC
LIMIT n OFFSET m;

Multi table queries

SELECT column, another_table_column
FROM table
INNER JOIN another_table ON table.at_id = another_table.id;

Possible joins: LEFT, RIGHT, FULL.

See also: Say no to Venn Diagrams on Join

Subqueries

There are a lot of questions you can’t answer with one simple SQL query, for example when you need to GROUP BY two different columns. Solution: put more queries in your query!

Here’s an example of comparing common cat names and dog names by doing two GROUP BYs:

WITH dog_counts AS (
    SELECT name, count(*) as dogs_with_name
    FROM dogs GROUP BY 1
),
cat_counts AS (
    SELECT name, count(*) as cats_with_name
    FROM cats GROUP BY 1
)
SELECT dog_counts.name, dogs_with_name, cats_with_name
FROM cat_counts FULL OUTER JOIN dog_counts USING (name)
ORDER BY dogs_with_name

These queries-inside-queries are called subqueries and here are a few places you can put them:

In a WITH statement before the query:

WITH table1 as (<subquery>),
table2 as (<subquery>)
SELECT ... FROM table1 LEFT JOIN table2 ...

Inline in a FROM:

SELECT ...
FROM (<subquery>)
GROUP BY ...

In a WHERE (the subquery has to return just 1 column here):

SELECT ...
FROM table
WHERE name IN (<subquery>)

Views

CREATE VIEW <view_name>
AS <select_statement>
SELECT ...

Materialized Views

See: The Benefits of Materialized Views (and how to use them in your Ruby on Rails project)

Common queries

Listing rows with duplicated values

SELECT title
FROM tracks
GROUP BY title
HAVING count(id) > 1;

SELECT a.*
FROM tracks a
JOIN (
    SELECT title, count(id)
    FROM tracks
    GROUP BY title
    HAVING count(id) > 1) b ON a.title = b.title
ORDER BY title;

Resources

Videos

• Things every developer absolutely, positively needs to know about database indexing

Lectures

• CS186 Berkeley on YouTube