This repository is a quick personal reference to using Postgres and elaborating SQL queries.
- SQL and Postgres Quick Reference
$ docker pull postgres:alpine
$ docker run --name postgres-0 -e POSTGRES_PASSWORD=password -d -p 5432:5432 postgres:alpine
$ docker exec -it postgres-0 bash
$ psql -U postgres
$ CREATE DATABASE test;
$ psql -h localhost -p 5432 -U postgres
| Action | PostgreSQL command |
|---|---|
| Help - list commands | \? |
| Access the PostgreSQL server from psql | psql -U [username]; |
| Connect to a specific database | \c database_name; |
| Quit the psql | \q |
| List all databases | \l |
| Describe database | \d |
| Describe table | \d table_name |
| List/describe all tables in current database | \dt |
| Execute file | \i file_path |
| Extended display on (transpose) | \x |
| Describe available functions | \df |
Link to complete Postgres Cheat Sheet
$ CREATE DATABASE test;
$ psql -h localhost -p 5432 -U postgres test
CREATE TABLE person (
id INT,
fisrt_name VARCHAR(50),
lastname VARCHAR(50),
gender VARCHAR(7),
date_of_birth DATE
);DROP TABLE person;CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY,
fisrt_name VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
gender VARCHAR(7) NOT NULL,
date_of_birth DATE NOT NULL,
email VARCHAR(50)
);INSERT INTO person (fisrt_name, lastname, gender, date_of_birth, email)
VALUES ('André', 'Kunitz', 'MALE', DATE '1990-04-04', '[email protected]');SELECT * FROM person;
SELECT fisrt_name, lastname FROM person;At this stage, we will create fake date to interact with the database e create several queries
-
To create mocked date, we can use Mockaroo: Create mock data with Mockaroo
-
For the purpose of this training, we can import data for the person table: person.sql file
-
To execute a SQL script file in Postgres, we can use the
\icommand:
$ \i ~/Desktop/person.sql
SELECT * FROM person ORDER BY country_of_birth;
SELECT * FROM person ORDER BY country_of_birth ASC;
SELECT * FROM person ORDER BY id DESC;
SELECT * FROM person ORDER BY first_name;
SELECT * FROM person ORDER BY id, email;SELECT DISTINCT country_of_birth FROM person ORDER BY country_of_birth DESC;SELECT * FROM person WHERE gender = 'Male';
SELECT * FROM person WHERE gender = 'Male' AND country_of_birth = 'Brazil';
SELECT * FROM person WHERE gender = 'Male' AND (country_of_birth = 'Brazil' OR country_of_birth = 'Canada');-- Limits to 10 results (LIMIT nowadays is widely support throughout databases)
SELECT * FROM person LIMIT 10;
-- Discard first 5 results and limits to 5 results (6, 7, 8, 9, 10)
SELECT * FROM person OFFSET 5 LIMIT 5;
-- Official limit method with FETCH (SQL Standard)
SELECT * FROM person OFFSET 5 FETCH FIRST 5 ROW ONLY;
SELECT * FROM person OFFSET 5 FETCH FIRST 1 ROW ONLY;
SELECT * FROM person OFFSET 5 FETCH FIRST ROW ONLY;Array of values and returns results matching these values
SELECT * FROM person
WHERE country_of_birth IN ('Brazil', 'China', 'France', 'Mexico')
ORDER BY country_of_birth;Selects data from a range
SELECT * FROM person
WHERE date_of_birth BETWEEN DATE '2000-01-01' AND '2022-01-01';An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.
SELECT * FROM person WHERE email LIKE '%@google.%';
-- Use underscore to match single characters (8 chars followed by @)
SELECT * FROM person WHERE email LIKE '_______@';
-- ILIKE ignores uppercase
SELECT * FROM person WHERE country_of_birth ILIKE 'p%';SELECT country_of_birth, COUNT(*) FROM person
GROUP BY country_of_birth
ORDER BY country_of_birth;Specifies a search condition for a group.
SELECT country_of_birth, COUNT(*) FROM person
GROUP BY country_of_birth
HAVING COUNT(*) > 5
ORDER BY country_of_birth;The UNION operator is used to combine the result-set of two or more SELECT statements.
- Every
SELECTstatement withinUNIONmust have the same number of columns - The columns must also have similar data types
- The columns in every
SELECTstatement must also be in the same order
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;The UNION operator selects only distinct values by default (aka.: SELECT DISTINCT). To allow duplicate values, use UNION ALL:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;SELECT MAX(price) FROM car;
SELECT MIN(price) FROM car;
SELECT AVG(price) FROM car;
SELECT ROUND(AVG(price)) FROM car;
SELECT make, MIN(price) FROM car GROUP BY make;SELECT SUM(price) FROM car;
SELECT make, SUM(price) FROM car GROUP BY make;SELECT 10 + 2;
SELECT 5!;
SELECT 10²;
SELECT 10 % 4;SELECT make, model, price, ROUND(price * .10, 2), ROUND((price - price * .10), 2) FROM car;SELECT
make,
model,
price AS original_price,
ROUND(price * .10, 2) AS ten_percent,
ROUND((price - price * .10), 2) AS discount_after_ten_percent
FROM car;Coalesce to the first value which is present.
-- returns 1
SELECT COALESCE(null, null, 1, 10) AS number;
SELECT COALESCE(email, 'Email not provided') FROM person;Takes two arguments and returns the first argument if the second is not equal to the first.
-- returns null
SELECT NULLIF(10, 10);
-- returns 10
SELECT NULLIF(10, 20);
-- Handling division by 0
SELECT COALESCE(10 / NULLIF(0, 0), 0);SELECT NOW();
SELECT NOW()::DATE;
SELECT NOW()::TIME;SELECT NOW() - INTERVAL '1 YEAR';
SELECT NOW() - INTERVAL '10 MONTHS';
SELECT NOW()::DATE + INTERVAL '10 DAYS';
SELECT (NOW() + INTERVAL '10 DAYS')::DATE;SELECT EXTRACT(YAER FROM NOW());
SELECT EXTRACT(DOW FROM NOW());
SELECT EXTRACT(CENTURY FROM NOW());SELECT first_name, last_name, date_of_birth, AGE(NOW(), date_of_birth) AS age FROM person;
-- then extract fields from datesCREATE TABLE customers(
customer_id INT GENERATED ALWAYS AS IDENTITY,
customer_name VARCHAR(255) NOT NULL,
PRIMARY KEY(customer_id)
);
CREATE TABLE contacts(
contact_id INT GENERATED ALWAYS AS IDENTITY,
customer_id INT,
contact_name VARCHAR(255) NOT NULL,
phone VARCHAR(15),
email VARCHAR(100),
PRIMARY KEY(contact_id),
CONSTRAINT fk_customer
FOREIGN KEY(customer_id)
REFERENCES customers(customer_id)
);DROP DATABASE database_name;
DROP TABLE table-name;-- Add column to table
ALTER TABLE table_name ADD column_name datatype;
-- Remove column from table
ALTER TABLE table_name DROP COLUMN column_name;
-- Change column data type
ALTER TABLE table_name ALTER COLUMN column_name datatype;ALTER TABLE person DROP CONSTRAINT person_pkey;
ALTER TABLE person ADD CONSTRAINT PRIMARY KEY (id);-- Gives unique constraint name
ALTER TABLE person ADD CONSTRAINT unique_email_address UNIQUE (email);
ALTER TABLE person DROP CONSTRAINT unique_email_address;
-- Database generates constraint name
ALTER TABLE person ADD UNIQUE (email);Check constraint allow us only to add a string that matches the argument.
ALTER TABLE person ADD CONSTRAINT gender_constraint CHECK (gender == 'Female' OR 'Male');SELECT column1, column2, ... FROM table_name;INSERT INTO person (first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES ('John', 'Doe', 'Male', '[email protected]', DATE '1960-01-01', 'Canada');
-- Multirow insert
INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);-- BE CAREFUL: Deletes every record in the table
DELETE FROM person;
-- Selectively deletes record
DELETE FROM person WHERE id = 1;-- BE CAREFUL: Update every entry in email column
UPDATE person SET email = '[email protected]';
-- Selectively deletes record
UPDATE person SET email = '[email protected]' WHERE id = 1;
-- Update multiple columns
UPDATE person SET email = '[email protected]', name = 'John Doe' WHERE id = 1;Handle errors. To use it, column must be a primary key or have a unique constraint.
INSERT INTO person (id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (1, 'John', 'Doe', 'Male', '[email protected]', DATE '1960-01-01', 'Canada')
ON CONFLICT (id) DO NOTHING;
INSERT INTO person (id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (1, 'John', 'Doe', 'Male', '[email protected]', DATE '1960-01-01', 'Canada')
ON CONFLICT (email) DO NOTHING;In this case, we want to update certain fields if the insert encounters an error.
-- email refers to the current email and EXCLUDED.email refers to the one inste de VALUES array
INSERT INTO person (id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (1, 'John', 'Doe', 'Male', '[email protected]', DATE '1960-01-01', 'Canada')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;
INSERT INTO person (id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (1, 'John', 'Doe', 'Male', '[email protected]', DATE '1960-01-01', 'Canada')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, first_name = EXCLUDED.first_name;To follow along, remove the current data and recreate tables and date using the given script.
DROP TABLE person;
DROP TABLE car;$ \i ~/Desktop/person-car.sql
UPDATE person SET car_id = 2 WHERE id = 1;
UPDATE person SET car_id = 1 WHERE id = 2;
-- Won't work. A car can only be owned by one person due to unique constraint.
UPDATE person SET car_id = 2 WHERE id = 2;Links two tables where primary key and the foreign key is found in both tables.
SELECT * FROM person
JOIN car ON person.car_id = car.id;
SELECT person.first_name, car.make, car.model FROM person
JOIN car ON person.car_id = car.id;Links two tables including records that don't have a foreign key relationship (aka.: also includes people that don't have a car).
SELECT * FROM person
LEFT JOIN car ON person.car_id = car.id;
SELECT * FROM person
LEFT JOIN car ON person.car_id = car.id
WHERE car.* IS NULL;DELETE FROM person WHERE id = 1;
DELETE FROM car WHERE id = 2;SELECT * FROM person_id_seq;
SELECT nextval('person_id_seq'::regclass);
SELECT * FROM person_id_seq;
-- restart sequence
ALTER SEQUENCE person_id_seq RESTART WITH 10;
SELECT * FROM person_id_seq;SELECT * FROM pg_available_extensions ORDER BY name;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();Example: person-car-uuid.sql
SELECT * FROM person LEFT JOIN car ON person.car_uid = car.car_uid;
-- Since the column has the same name in both tables, we can use:
SELECT * FROM person LEFT JOIN car USING (car_uid);
SELECT * FROM person LEFT JOIN car USING (car_uid) WHERE car.* IS NULL;\copy (SELECT * FROM person LEFT JOIN car ON person.car_uid = car.car_uid;) TO '~/Desktop/results.csv' DELIMITER ',' CSV HEADER;The content of this repository was based on this course and some personal research.