SQL Command Cheatsheet - PostgreSQL

postgres_commands.md

postgreSQL commands

Source: https://www.youtube.com/watch?v=qw--VYLpxG4

List databses

\l

Create database

CREATE DATABASE test;

Connect to server

psql -h localhost -p 5432 -U ui_library_user test

Connect to specific database

\c test

Delete db

DROP DATABASE test;

Create table

CREATE TABLE person (
  id BIGSERIAL NOT NULL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  gender VARCHAR(6) NOT NULL,
  date_of_birth DATE NOT NULL );

View tables

\d
\dt

View table

\d person

ALTER TABLE

ALTER TABLE person ADD email VARCHAR(150);
ALTER TABLE person
  ALTER COLUMN id POSITION 1

Inserting

INSERT INTO person (
  first_name,
  last_name,
  gender,
  date_of_birth)
VALUES ('Anne', 'Smith', 'FEMALE', DATE '1988-01-09');

View data

SELECT * FROM person;
SELECT first_name, last_name FROM person;

Add from file

\i C:\Users\Username\downloads\person.sql

Quit help menu

q

ORDER

SELECT * FROM person ORDER BY country_of_birth;
SELECT * FROM person ORDER BY country_of_birth ASC;
SELECT * FROM person ORDER BY country_of_birth DESC;
SELECT * FROM person ORDER BY gender, email ASC;

DISTINCT

SELECT DISTINCT country_of_birth FROM person ORDER BY country_of_birth;

WHERE

SELECT * FROM person WHERE gender = 'Female';
SELECT * FROM person WHERE gender <> 'Female';
SELECT * FROM person WHERE gender = 'Female' AND country_of_birth = 'Poland';
SELECT * FROM person WHERE gender = 'Female' AND country_of_birth = 'Poland' OR country_of_birth = 'China';

LIMIT, OFFSET, FETCH

SELECT * FROM person LIMIT 10;
SELECT * FROM person OFFSET 5 LIMIT 10;
SELECT * FROM person OFFSET 5 FETCH FIRST 5 ROW ONLY;

IN

SELECT * FROM person WHERE country_of_birth IN ('China', 'Brazil', 'France');

BETWEEN

SELECT * FROM person WHERE date_of_birth BETWEEN DATE '2022-11-01' AND '2023-01-01';

LIKE / ILIKE

SELECT * FROM person WHERE email LIKE '%.com';
SELECT * FROM person WHERE email LIKE '%@bloomberg.com';

% is the wildcard

SELECT * FROM person WHERE email LIKE '%@google.%';

num of characters

SELECT * FROM person WHERE email LIKE '______@%';

case insensitive

SELECT * FROM person WHERE country_of_birth ILIKE 'p%';

GROUPBY

SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth ORDER BY country_of_birth;
SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth ORDER BY count DESC;

HAVING

SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth HAVING COUNT(*) > 5;

MAX / MIN

SELECT MAX(price) FROM car;
SELECT MIN(price) FROM car;
SELECT AVG(price) FROM car;
SELECT ROUND(AVG(price)) FROM car;
SELECT make, model, MIN(price) FROM car GROUP BY make, model;

SUM

SELECT SUM(price) FROM car;
SELECT make, SUM(price) FROM car GROUP BY make;

ARITHMETIC

SELECT 10 + 2;
SELECT 4 + 5 * 5;
SELECT 10 % 3;

SELECT id, make, model, price, price * .10 FROM car;
SELECT id, make, model, price, ROUND(price * .10, 2), ROUND(price - (price * .1), 2) FROM car;

ALIAS

SELECT id, make, model, price AS original_price,
ROUND(price * .10, 2) AS ten_percent,
ROUND(price - (price * .1), 2) AS discount_after_10_percent
FROM car;

COALESCE

SELECT COALESCE(null, 1) AS number;
SELECT COALESCE(null, null, 1, 10) AS number;
SELECT COALESCE(email, 'Email not provided') FROM person;

NULLIF

• If the numbers are the same, null

SELECT 10 / 0;
SELECT NULLIF(10, 10); // null
SELECT 10 / NULLIF(0, 0); // undefined
SELECT COALESCE(10 / NULLIF(0,0), 0);

DATES / TIMESTAMP

SELECT NOW();
SELECT NOW()::DATE;
SELECT NOW()::TIME;
SELECT NOW() - INTERVAL '1 YEAR';
SELECT NOW() - INTERVAL '10 YEARS';
SELECT NOW() - INTERVAL '10 MONTHS';
SELECT NOW() + INTERVAL '10 DAYS';
SELECT NOW()::DATE + INTERVAL '10 DAYS';
SELECT (NOW()::DATE + INTERVAL '10 DAYS')::DATE;

Extracting Fields

SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(DOW FROM NOW());
SELECT EXTRACT(CENTURY FROM NOW());

AGE FUNCTION

SELECT first_name, last_name, gender, country_of_birth, date_of_birth, AGE(NOW(), date_of_birth) FROM person;

PRIMARY KEY (PK)

• Uniquely identifiers a record in tables

ALTER TABLE person DROP CONSTRAINT person_pkey;
ALTER TABLE person ADD PRIMARY KEY (id);

DELETE

• Use primary key in most cases

DELETE FROM person WHERE id = 1;
DELETE FROM person WHERE gender = 'Male' AND country_of_birth = 'Canada';

Unique Constraint

SELECT email, count(*) FROM person GROUP BY email HAVING COUNT(*) > 1;
ALTER TABLE person ADD CONSTRAINT unique_email_address UNIQUE (email);
ALTER TABLE person ADD UNIQUE (email);
\d person
ALTER TABLE person DROP CONSTRAINT unique_email_address;

Check Constraint

ALTER TABLE person ADD CONSTRAINT gender_constraint CHECK (gender = 'Female' OR gender = 'Male');

UPDATE

UPDATE person SET email = '[email protected]' WHERE id = 201;
SELECT * FROM person WHERE id = 201;

UPDATE person SET first_name = 'Omar', last_name = 'Montana', email = '[email protected]' WHERE id = 201;

On Conflict Do Nothing

• Column needs to be unique for it to work - either via primary key or unique constraint

SELECT * FROM person WHERE id = 100;
INSERT INTO person(id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (100, 'Ross', 'Ruddoch', 'Male', '[email protected]', DATE '1952-09-25', 'Norway');

INSERT INTO person(id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (100, 'Ross', 'Ruddoch', 'Male', '[email protected]', DATE '1952-09-25', 'Norway')
ON CONFLICT (id) DO NOTHING;

UPSERT - UPDATE or INSERT

INSERT INTO person(id, first_name, last_name, gender, email, date_of_birth, country_of_birth)
VALUES (100, 'Ross', 'Ruddoch', 'Male', '[email protected]', DATE '1952-09-25', 'Norway')
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 (100, 'Ross', 'Ruddoch', 'Male', '[email protected]', DATE '1952-09-25', 'Norway')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, last_name = EXCLUDED.last_name;

FOREIGN KEYS & JOINS

car_id BIGINT REFERENCES car(id) UNIQUE (car_id)

UPDATING FORIEGN KEYS

• Changing the person's car_id to own car 1 instead

UPDATE person SET car_id = 2 WHERE id = 1;

INNER JOINS

• Combining two tables where primary key and foreign key is found in both tables
• Not all rows in table A will show up. Those without a matching foreign key will be excluded
• Excludes those without a foreign key

SELECT * FROM person
JOIN car ON person.car_id = card.id;

SELECT person.first_name, car.make, car.model, car.price
FROM person
JOIN car ON person.car_id = car.id;

Expanded Display

\x

LEFT JOINS

• Left join includes all columns from table A and columns with data found in table B
• Includes those without a foreign key
• So all rows in table A will be in the final table
• car.* means every single column

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 WITH FOREIGN KEY CONSTRAINT

• Must delete row that has the foreign key first or remove the foreign key for that row

TRUNCATE

• Delete all rows at once

TRUNCATE TABLE table_name;

Export to CSV

\copy (SELECT * FROM person LEFT JOIN car ON car.id = person.car_id) TO '/Users/name/Desktop' DELMIITER ',' CSV HEADER;

Serial & Sequences

• Incremented the id so the next insert will skip the number of times we invoked the function

SELECT * FROM person_id_seq;
SELECT nextval('person_id_seq'::regclass);

ALTER SEQUENCE person_id_seq RESTART WITH 999;

Extensions

SELECT * FROM pg_available_extensions;

UUIDs

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
\df
SELECT uuid_generate_v4();

CREATE TABLE car (
  car_uid UUID NOT NULL PRIMARY KEY,
)

CREATE TABLE person(
  person_uid UUID NOT NULL PRIMARY KEY,
  car_uid UUID REFERENCES car(car_uid),
)

INSERT INTO person (person_uid) VALUES (uuid_generate_v4());

SELECT * FROM person
JOIN car USING (car_uid);