Skip to content

Xavier-Pierre-dev/PostgreSQL-cheat-sheet

BranchesTags

Repository files navigation

🚀 POSTGRESQL (SQL) Cheatsheet 🚀

image



Requirement

Environment variable (Windows)

Environment variable Zone

image

Zone 1

Variable Value
Path C:\Program Files\PostgreSQL\12\bin (Your own postgreSQL bin path)
Environment variable : path

image



Context explaination

image

  • Person has car
  • Person can only have one car
  • Car can belong to one person only

Note :

  • I have conscience that this rules maybe are disapointing because normaly 'Person can have car' mean 0, 1 or more. But here we see that the FK who reference a car was inside the table person, right? And a person should be unique inside our table so it's mean : 'Person can only have one car'.
  • One solution for this concern will be to simply put the FK inside the car table, so it's will be a car who will reference a person or not. But in our case one of the sources we use for learning with practice using this great ressources : [FCC] Learn PostgreSQL Tutorial - Full Course for Beginners [EN][Amigoscode] that's why we keep the same context given inside the video because if a concept seem complexe when we are going back to it we can simply go to the video at the right section and get some hint.
  • Left : current relationship (The one used)
  • Right : normaly what should be more correct (Just provided for information)

Around the end we use UUID function for generate "id" but not at start, like it's a step by step learning path with incremental methodology. The cheatsheet follow the same behaviour.

Ressources

⭐️ Free Content : [FCC] Learn PostgreSQL Tutorial - Full Course for Beginners [EN][Amigoscode] ⭐️
⭐️ Paid Content : Advanced Databases [EN][Amigoscode] ⭐️

Advanced Database In this course you will learn advanced database concepts that you must know to write production ready applications. As a software engineer knowing these concepts will make sure you write solid backend application and fully understand what goes on behind the scenes. You will learn the following:

  • Joins
  • Indexes
  • Transactions
  • Database Administration
  • Functions / Stored Procedures
  • Schemas
  • Database Backups

Advanced Database : Amigoscode need to be paid courses for following the video.

⭐️ Paid Content : Database Design & Implementation [EN][Amigoscode] ⭐️

Database Design & Implementation Backend starts from Your Database. When you have a solid database design coding is easy. In this course you will learn how to design databases for your backend applications. You will learn how to design a subset of YouTube Platform allowing you design your own databases.

  • Capture Entities
  • Designing ERD
  • ERD Cardinalities
  • One to One Relationships
  • One to Many Relationships
  • Many to Many Relationships
  • Defining Constraints
  • Database Normalisation
  • SQL Implementation

Database Design & Implementation : Amigoscode need to be paid courses for following the video.

⭐️ Free Content : Tutorials and PostgreSQL training [FR][xavki] ⭐️

Tutorial in French to start and progress on PostgreSQL. Many tips, definitions and concepts: psql, installation, tables, databases, pg_hba, postgresql, replication, archiving, backups, restore...

Tutorials and PostgreSQL training [FR][xavki]

⭐️ Free Content : Online exercice for SQL practice [EN][pgexercises] ⭐️

Free Content : Online exercises for SQL practice [EN][pgexercices]

Note : I'm sorry but when I learn postgresql and sql using Amigoscode content on youtube i find him content awesome and straightforward. So i includ here him link to the paid content too if people want to thanks him awesome content. And for be able to see it, you will need to enroll on Amigoscode course.



Mockaroo

We can use the website Mockaroo for generating our table and some data :

  • person :
id first_name Last_name email gender date_of_birth country_of_birth

image

after that we do some ajustement on person.sql file to fit the previous schemas :

create table person (
	id BIGSSERIAL NOT NULL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	email VARCHAR(150),
	gender VARCHAR(50) NOT NULL,
	date_of_birth DATE NOT NULL,
	country_of_birth VARCHAR(50),
    car_id BIGINT REFERENCES car (id),
    UNIQUE(car_id),
    UNIQUE(email)
);

  • car :
id make model price

and for car.sql file :

create table car (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	make VARCHAR(100) NOT NULL,
	model VARCHAR(100) NOT NULL,
	price NUMERIC(19, 2) NOT NULL
);

Launch script

windows

\i 'C:\\Temp\\person.sql'

mac & linux

\i C:/Temp/person.sql


Create database

CREATE DATABASE name_of_db;

check list of db

\l


Connect to database

url command line method

psql postgresql://USERNAME:PASSWORD@ADRESSE:PORT/DB_Name
  • USERNAME : your username the one you use when installing postgresql or if you modify that or create a new username later
  • PASSWORD : your password the one you use when installing postgresql or if you modify that or create a new username later
  • ADRESSE : adresse where is hosted your database localhost can work
  • PORT : port for your database can be define inside your pgAdmin UI but default value is 5432
  • DB_Name : the name of your database you want to connect

command line method

psql -h ADRESSE -p PORT -U USERNAME DB_Name

this method will ask you a password if you haven't setup a .pgpass file or pg_hba.conf file but if you have setup one then you don't need a password.

Setup pgpass file

Setup pg_hba file



Check

list of db

\l

list dataTable and sequencies

\d

Alternative : show only table

\dt

show a table

\d person

Activate / Desactivate extended view

\x

Clear console

\! cls


SQL Basics keyword

CREATE TABLE

Create table without constraint 
CREATE TABLE person (
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(7),
date_of_birth DATE);
Create table with constraint and primary key 
CREATE TABLE person (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	email VARCHAR(150),
	gender VARCHAR(50) NOT NULL,
	date_of_birth DATE NOT NULL,
	country_of_birth VARCHAR(50),
);
CREATE TABLE person (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	email VARCHAR(150),
	gender VARCHAR(50) NOT NULL,
	date_of_birth DATE NOT NULL,
	country_of_birth VARCHAR(50),
    car_id BIGINT REFERENCES car(id),
    UNIQUE(car_id),
    UNIQUE(email)
);

BIGSERIAL : can be use for ID because automatics incrementation inside the bdd

UUID : method for generating unique ID can be use for PK Primary Keys



DELETE TABLE

DROP TABLE person
  • person : name of the table


ADD one person as a ROW (Record)

INSERT INTO person (
first_name, last_name, gender, date_of_birth, email)
VALUES ('Jake', 'Jones', 'MALE', DATE '1990-01-10', 'jake@gmail.com');


SELECT

command :

SELECT first_name, last_name FROM person;

output : 
  first_name  |   last_name
--------------+----------------
 Xylia        | Sprigging
 Arlette      | Elcoux
 Maxim        | Shillabear
 Mannie       | Avrahamoff
 Orrin        | Fayre
 Heindrick    | Pulfer
 Evelyn       | Erington
 Gerhard      | Daice
...
...


ORDER BY

ordering by default : ascending

  • 1 --> 2 --> 3 --> 4 --> ...
  • A --> B --> C --> D --> ...

command :

command for ordering by ascending ASC order 
SELECT * FROM person ORDER BY country_of_birth;

and

SELECT * FROM person ORDER BY country_of_birth ASC;
output : 
 id  |  first_name  |   last_name    |               email               |   gender    | date_of_birth |    country_of_birth
------+--------------+----------------+-----------------------------------+-------------+---------------+------------------------
  976 | Sharia       | Schimonek      | sschimonekr3@thetimes.co.uk       | Bigender    | 2021-08-16    | Afghanistan
  272 | Libbey       | Strowlger      | lstrowlger7j@youtube.com          | Genderqueer | 2021-06-25    | Afghanistan
  474 | Winnie       | Filby          | wfilbyd5@ibm.com                  | Non-binary  | 2021-09-06    | Afghanistan
   77 | Romy         | Faraday        |                                   | Non-binary  | 2020-12-18    | Afghanistan
  799 | Nikaniki     | Kerwood        | nkerwoodm6@tinypic.com            | Bigender    | 2021-09-22    | Aland Islands
  293 | Lock         | Brunnen        | lbrunnen84@soup.io                | Polygender  | 2021-07-27    | Albania
  163 | Ase          | Shinner        | ashinner4i@soundcloud.com         | Polygender  | 2020-11-01    | Albania
  981 | Edvard       | Quiddihy       | equiddihyr8@de.vu                 | Genderqueer | 2021-06-08    | Albania
   33 | Bertine      | Nancekivell    |                                   | Male        | 2021-03-07    | Albania
  972 | Bernarr      | Stoile         |                                   | Bigender    | 2021-09-19    | Albania
  399 | Erie         | Wogdon         |                                   | Agender     | 2020-12-01    | Albania
  916 | Corabelle    | O' Mara        |                                   | Female      | 2021-03-22    | Andorra
  402 | Kerstin      | Bernade        | kbernadeb5@feedburner.com         | Genderfluid | 2021-07-22    | Angola
  534 | Augustine    | Orkney         | aorkneyet@businessinsider.com     | Agender     | 2021-07-05    | Anguilla
  721 | Kevyn        | Carletti       | kcarlettik0@deviantart.com        | Bigender    | 2020-11-30    | Argentina
  871 | Domenico     | Maty           |                                   | Polygender  | 2020-11-13    | Argentina
...
...

Note : order by ascending by default so you don't really need to use ASC keyword

we can also

SELECT * FROM person ORDER BY country_of_birth, email;
command for ordering by ascending DSC order 
SELECT * FROM person ORDER BY country_of_birth DESC;

Note : order by ascending by default so you don't really need to use DESC keyword



DISTINCT

description : remove duplicate inside preview

command :

SELECT DISTINCT country_of_birth FROM person ORDER BY country_of_birth;

output : 
    country_of_birth
------------------------
 Afghanistan
 Aland Islands
 Albania
 Andorra
 Angola
 Anguilla
 Argentina
 Armenia
 Bahamas
 Bangladesh
 Belarus
 Bosnia and Herzegovina
 Botswana
 Brazil
...
...


WHERE CLAUSE

description : filter by value

Note : WHERE CLAUSE is case sensitive

Filter with one condition on gender

command :

SELECT * FROM person WHERE gender='Female';
output :

output :

 id  | first_name  |   last_name   |              email               | gender | date_of_birth | country_of_birth
-----+-------------+---------------+----------------------------------+--------+---------------+------------------
   7 | Evelyn      | Erington      | eerington6@answers.com           | Female | 2021-09-23    | China
  10 | Udall       | Vizor         | uvizor9@harvard.edu              | Female | 2021-07-28    | Iran
  20 | Chrissie    | Grouen        | cgrouenj@php.net                 | Female | 2021-02-28    | Russia
  21 | Pail        | Coade         | pcoadek@mtv.com                  | Female | 2021-06-01    | Russia
  35 | Alexandrina | Alltimes      | aalltimesy@senate.gov            | Female | 2020-11-03    | China
  43 | Shellie     | Georgeot      | sgeorgeot16@senate.gov           | Female | 2021-01-31    | Mexico
  49 | Merilyn     | Downing       | mdowning1c@dagondesign.com       | Female | 2020-12-21    | Russia
  73 | Shaylah     | Gilfoyle      | sgilfoyle20@cbc.ca               | Female | 2021-04-29    | Sweden
  79 | Salem       | McCart        | smccart26@devhub.com             | Female | 2020-11-26    | China
  81 | Tish        | McGlynn       |                                  | Female | 2021-09-26    | Laos
  82 | Zonnya      | Whistance     | zwhistance29@vk.com              | Female | 2021-05-17    | Thailand
...

Filter with multiple condition AND

command :

SELECT * FROM person WHERE gender='Female' AND country_of_birth='Poland';

Filter with multiple condition AND & OR combination

command :

SELECT * FROM person WHERE gender='Female' AND (country_of_birth='Poland' OR country_of_birth='China');


Comparison

command

SELECT 1 = 2;

other operator mathematics possible :

operator description
> greater than
>= greater than or equal
< less than
<= less than or equal
<> not equal

Note : can be use on any datatype


output: 
 ?column?
----------
 f
(1 ligne)

Note :

  • f for false
  • t for true


LIMIT and OFFSET keywords

command :

SELECT 10 records (row) fron person

command :

SELECT * FROM person LIMIT 10;

output : 
 id | first_name | last_name  |            email            |   gender    | date_of_birth | country_of_birth
----+------------+------------+-----------------------------+-------------+---------------+------------------
  1 | Xylia      | Sprigging  | xsprigging0@paginegialle.it | Polygender  | 2020-10-25    | Zimbabwe
  2 | Arlette    | Elcoux     | aelcoux1@cbsnews.com        | Genderqueer | 2020-11-02    | Eritrea
  3 | Maxim      | Shillabear | mshillabear2@google.es      | Agender     | 2020-12-15    | United States
  4 | Mannie     | Avrahamoff |                             | Polygender  | 2021-07-12    | Russia
  5 | Orrin      | Fayre      | ofayre4@dyndns.org          | Genderfluid | 2021-08-29    | Philippines
  6 | Heindrick  | Pulfer     |                             | Male        | 2021-03-13    | China
  7 | Evelyn     | Erington   | eerington6@answers.com      | Female      | 2021-09-23    | China
  8 | Gerhard    | Daice      | gdaice7@japanpost.jp        | Agender     | 2021-10-11    | France
  9 | Pietro     | Borris     |                             | Genderqueer | 2020-11-02    | Japan
 10 | Udall      | Vizor      | uvizor9@harvard.edu         | Female      | 2021-07-28    | Iran
(10 lignes)

SELECT 10 records (row) fron person with an OFFSET of 5 so start to the 6th records and FETCH alternative command 
SELECT * FROM person OFFSET 5 LIMIT 10;

Note : LIMIT wasn't the normalise command for it on SQL, normaly it's FETCH but like LIMIT was really user friendly this one become popular and been implement almost on every database. See the fetch command below

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

SELECT the 10 lasts records from person 
SELECT * FROM person ORDER BY id DESC LIMIT 10;

SELECT email FROM person WHERE email IS NOT NULL ORDER BY id DESC Limit 10;


IN

command :

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


BETWEEN

retrieve person with id BETWEEN 6 and 40 
SELECT * FROM person WHERE id BETWEEN '6' AND '40';
output : 
 id | first_name  |   last_name   |            email             |   gender    | date_of_birth |    country
----+-------------+---------------+------------------------------+-------------+---------------+----------------
  6 | Heindrick   | Pulfer        |                              | Male        | 2021-03-13    | China
  7 | Evelyn      | Erington      | eerington6@answers.com       | Female      | 2021-09-23    | China
  8 | Gerhard     | Daice         | gdaice7@japanpost.jp         | Agender     | 2021-10-11    | France
  9 | Pietro      | Borris        |                              | Genderqueer | 2020-11-02    | Japan
 10 | Udall       | Vizor         | uvizor9@harvard.edu          | Female      | 2021-07-28    | Iran
 11 | Virginie    | McFee         | vmcfeea@soup.io              | Polygender  | 2021-09-19    | Czech Republic
 12 | Quincey     | Tavinor       | qtavinorb@google.de          | Non-binary  | 2021-03-14    | China
 13 | Duffy       | Reven         |                              | Male        | 2021-01-31    | Indonesia
 14 | Kissee      | Anersen       | kanersend@sciencedaily.com   | Agender     | 2020-12-08    | South Sudan
 15 | Alaric      | Leil          | aleile@github.io             | Genderfluid | 2020-12-09    | Philippines
 16 | Doe         | Mawman        | dmawmanf@constantcontact.com | Genderfluid | 2020-11-07    | Japan
 17 | Atalanta    | Hallard       | ahallardg@elpais.com         | Genderfluid | 2021-04-07    | Cyprus
 18 | Wilfred     | Seear         | wseearh@ezinearticles.com    | Non-binary  | 2021-10-06    | Czech Republic
 19 | Kliment     | Behling       | kbehlingi@indiatimes.com     | Male        | 2021-05-14    | China
 20 | Chrissie    | Grouen        | cgrouenj@php.net             | Female      | 2021-02-28    | Russia
 21 | Pail        | Coade         | pcoadek@mtv.com              | Female      | 2021-06-01    | Russia
 22 | Vincent     | Payze         |                              | Bigender    | 2021-03-30    | Portugal
 23 | Anastassia  | Schieferstein | aschiefersteinm@alibaba.com  | Agender     | 2021-02-23    | Russia
 24 | Issie       | Worsnap       | iworsnapn@naver.com          | Male        | 2021-05-25    | Poland
 25 | Eugene      | Gookey        | egookeyo@comsenz.com         | Agender     | 2021-05-30    | Egypt
 26 | Junina      | Rollitt       | jrollittp@upenn.edu          | Bigender    | 2021-07-21    | Indonesia
 27 | Anett       | Arstingall    | aarstingallq@adobe.com       | Agender     | 2021-04-08    | France
 28 | Robyn       | Camies        |                              | Non-binary  | 2020-10-25    | Czech Republic
 29 | Petra       | Barrasse      | pbarrasses@gravatar.com      | Male        | 2021-07-22    | Indonesia
 30 | Ashlan      | Ahrenius      |                              | Non-binary  | 2021-05-19    | China
 31 | Linea       | Bierman       | lbiermanu@artisteer.com      | Genderqueer | 2021-01-03    | China
 32 | Ashton      | Harly         | aharlyv@harvard.edu          | Bigender    | 2021-06-29    | Armenia
 33 | Bertine     | Nancekivell   |                              | Male        | 2021-03-07    | Albania
 34 | Orrin       | Cleeton       |                              | Polygender  | 2021-10-07    | China
 35 | Alexandrina | Alltimes      | aalltimesy@senate.gov        | Female      | 2020-11-03    | China
 36 | Thorin      | Cattrell      |                              | Male        | 2021-06-06    | Indonesia
 37 | Larissa     | Peachment     |                              | Agender     | 2021-03-06    | Indonesia
 38 | Jdavie      | Ible          | jible11@fastcompany.com      | Bigender    | 2020-11-23    | Ukraine
 39 | Mariquilla  | Phare         |                              | Non-binary  | 2021-08-28    | El Salvador
 40 | Olympie     | Harwell       | oharwell13@usda.gov          | Agender     | 2020-11-10    | Cameroon
(35 lignes)
...

Note : 6 AND 40 are included when you write the command : SELECT * FROM person WHERE id BETWEEN '6' AND '40';


retrieve person with date_of_birth BETWEEN 2 DATES

command :

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

OR

SELECT * FROM person WHERE date_of_birth BETWEEN '2000-01-01' AND '2022-01-01';

output : 
  id  |  first_name  |   last_name    |               email               |   gender    | date_of_birth |        country
------+--------------+----------------+-----------------------------------+-------------+---------------+------------------------
    2 | Arlette      | Elcoux         | aelcoux1@cbsnews.com              | Genderqueer | 2020-11-02    | Eritrea
    3 | Maxim        | Shillabear     | mshillabear2@google.es            | Agender     | 2020-12-15    | United States
    4 | Mannie       | Avrahamoff     |                                   | Polygender  | 2021-07-12    | Russia
    5 | Orrin        | Fayre          | ofayre4@dyndns.org                | Genderfluid | 2021-08-29    | Philippines
    6 | Heindrick    | Pulfer         |                                   | Male        | 2021-03-13    | China
    7 | Evelyn       | Erington       | eerington6@answers.com            | Female      | 2021-09-23    | China
    8 | Gerhard      | Daice          | gdaice7@japanpost.jp              | Agender     | 2021-10-11    | France
    9 | Pietro       | Borris         |                                   | Genderqueer | 2020-11-02    | Japan
   10 | Udall        | Vizor          | uvizor9@harvard.edu               | Female      | 2021-07-28    | Iran
   11 | Virginie     | McFee          | vmcfeea@soup.io                   | Polygender  | 2021-09-19    | Czech Republic   
   12 | Quincey      | Tavinor        | qtavinorb@google.de               | Non-binary  | 2021-03-14    | China
   13 | Duffy        | Reven          |                                   | Male        | 2021-01-31    | Indonesia
   14 | Kissee       | Anersen        | kanersend@sciencedaily.com        | Agender     | 2020-12-08    | South Sudan
   15 | Alaric       | Leil           | aleile@github.io                  | Genderfluid | 2020-12-09    | Philippines
   16 | Doe          | Mawman         | dmawmanf@constantcontact.com      | Genderfluid | 2020-11-07    | Japan
   17 | Atalanta     | Hallard        | ahallardg@elpais.com              | Genderfluid | 2021-04-07    | Cyprus
   18 | Wilfred      | Seear          | wseearh@ezinearticles.com         | Non-binary  | 2021-10-06    | Czech Republic   
   19 | Kliment      | Behling        | kbehlingi@indiatimes.com          | Male        | 2021-05-14    | China
   20 | Chrissie     | Grouen         | cgrouenj@php.net                  | Female      | 2021-02-28    | Russia
   21 | Pail         | Coade          | pcoadek@mtv.com                   | Female      | 2021-06-01    | Russia
   22 | Vincent      | Payze          |                                   | Bigender    | 2021-03-30    | Portugal
...

Note : extremity are included with the Keywords BETWEEN see example with id



LIKE and ILIKE

retrieve all email ending by .com :

SELECT * FROM person WHERE email LIKE '%.com'
  • LIKE => case sensitive
  • ILIKE => case sensitive

match _ _ _ 3 characters following by @gmail.com

SELECT * FROM person WHERE email LIKE '___@gmail.com'


Group by

command :

SELECT country, COUNT(*) FROM person GROUP BY country;
output : 
        country         | count
------------------------+-------
 Bangladesh             |     7
 Indonesia              |   116
 Venezuela              |     8
 Cameroon               |     2
 Czech Republic         |    32
 Sweden                 |    26
 Montenegro             |     1
 Uganda                 |     4
 Jordan                 |     3
 Dominican Republic     |     3
 Cambodia               |     1
 Ireland                |     4
 Macedonia              |     2
 Sri Lanka              |     1
...

HAVING perform a filter after GROUP BY and need to be the next keyword after keyword GROUP BY

SELECT country, COUNT(*) FROM person GROUP BY country HAVING COUNT(*) > 5 ORDER BY country DESC;
output : 
        country         | count
------------------------+-------
 Bangladesh             |     7
 Indonesia              |   116
 Venezuela              |     8
 Cameroon               |     2
 Czech Republic         |    32
 Sweden                 |    26
 Montenegro             |     1
 Uganda                 |     4
 Jordan                 |     3
 Dominican Republic     |     3
 Cambodia               |     1
 Ireland                |     4
 Macedonia              |     2
 Sri Lanka              |     1
...

Note : Find other function than COUNT() and more information inside the documentation :



UPDATE / DELETE / ADD

ALTER a column

ALTER TABLE person ALTER COLUMN gender set data type VARCHAR (50);
ALTER TABLE person ADD COLUMN country_of_birth VARCHAR (50);
ALTER TABLE person RENAME COLUMN country_of_birth TO country;


DELETE

DELETE FROM person WHERE id = 1;


UPDATE

command :

UPDATE person SET email = 'test@gmail.com' WHERE id = 50;
UPDATE person SET email = 'test@gmail.com', first_name = 'John' WHERE id = 50;


Arithmetics operation & function

MIN / MAX / AVG / SUM

command

SELECT MAX(price) FROM car;
output : 
   max
----------
 99915.50
(1 ligne)
  • AVG
  • MIN
  • MAX
  • SUM

Note : Find other function than that and more information inside the documentation :


we can also use multiple function at same time example :

SELECT ROUND(AVG(price)) FROM car;
output : 
 round
-------
 54850
(1 ligne)

we can also cast the result of a function with :: operator : command :

SELECT AVG(price)::numeric(10,2) FROM car;
output : 
   avg
----------
 54850.39
(1 ligne)

command :

SELECT make, model, MIN(price) From car GROUP BY make, model;
output : 
     make      |        model         |   min
---------------+----------------------+----------
 Pontiac       | GTO                  | 32387.88
 Toyota        | Land Cruiser         | 51081.86
 Mercedes-Benz | S-Class              | 11810.67
 Infiniti      | FX                   | 64473.33
 BMW           | M6                   | 74583.10
 Saturn        | Aura                 | 94077.78
 Oldsmobile    | Silhouette           | 41962.28
 Daewoo        | Lanos                | 83250.58
 Subaru        | Forester             | 28593.87
 Ford          | Econoline E350       | 20043.51
 Ferrari       | 612 Scaglietti       | 14523.23
 Jeep          | Grand Cherokee       | 56399.60
 Honda         | Civic Si             | 70520.28
 Hyundai       | Accent               | 41019.17
 Isuzu         | Rodeo                | 11812.81
 Buick         | Electra              | 94782.37
 Buick         | Skyhawk              | 44523.51
...

command :

SELECT make, SUM(price) FROM car GROUP BY make;
output : 
     make      |    sum
---------------+------------
 Ford          | 4603175.77
 Smart         |   58257.27
 Maserati      |  193260.79
 Dodge         | 2594682.79
 Infiniti      |  679515.74
 MINI          |  350316.16
 Jensen        |   24731.35
 Bentley       |  256902.09
 Citroën      |   17770.50
 Pontiac       | 2371857.32
 Plymouth      |  311295.40
 Holden        |   30883.67
 Audi          | 1426739.61
 Lexus         | 1186254.56
 Fairthorpe    |   41446.52
 Jeep          |  402241.00
...
...


arithmetique operation

command :

SELECT 10 + 2;

output :

 ?column?
----------
       12
(1 ligne)

possibility :

operator description
/ division
* multiplication
+ addition
- subtraction
^ power
! factorial : 5! = 5 x 4 x 3 x 2 x 1
% modulo : 5 % 2 = 1 because 5 / 2 + 1 = 0

Note : Factorial can be used for different purpose like calcul of



command

SELECT make, model, price, ROUND(price / 10, 2) FROM car;

output

     make      |        model         |  price   |  round
---------------+----------------------+----------+---------
 Mitsubishi    | Galant               | 45861.07 | 4586.11
 Pontiac       | Sunfire              | 20427.86 | 2042.79
 Nissan        | Maxima               | 74412.54 | 7441.25
 Saab          | 900                  | 53428.67 | 5342.87
 Honda         | S2000                | 94521.76 | 9452.18
 GMC           | Jimmy                | 69066.03 | 6906.60
 Ford          | F-Series             | 63790.81 | 6379.08
 GMC           | Sonoma               | 68905.72 | 6890.57
 Ford          | Taurus               | 56825.50 | 5682.55
 Subaru        | Outback              | 34886.05 | 3488.61
 Nissan        | Rogue                | 89159.57 | 8915.96
 Pontiac       | Gemini               | 26245.82 | 2624.58
 Hyundai       | Azera                | 86235.96 | 8623.60
...
...


alias AS

command :

SELECT make, model, price, round(price / 10, 2) AS ten_percent FROM car;

VS

SELECT make, model, price, ROUND(price / 10, 2) FROM car;

output with AS keywords : 
     make      |        model         |  price   | ten_percent
---------------+----------------------+----------+-------------
 Mitsubishi    | Galant               | 45861.07 |     4586.11
 Pontiac       | Sunfire              | 20427.86 |     2042.79
 Nissan        | Maxima               | 74412.54 |     7441.25
 Saab          | 900                  | 53428.67 |     5342.87
 Honda         | S2000                | 94521.76 |     9452.18
 GMC           | Jimmy                | 69066.03 |     6906.60
 Ford          | F-Series             | 63790.81 |     6379.08
 GMC           | Sonoma               | 68905.72 |     6890.57
 Ford          | Taurus               | 56825.50 |     5682.55
 Subaru        | Outback              | 34886.05 |     3488.61
 Nissan        | Rogue                | 89159.57 |     8915.96
 Pontiac       | Gemini               | 26245.82 |     2624.58
 Hyundai       | Azera                | 86235.96 |     8623.60
...
VS
output without AS keywords : 
     make      |        model         |  price   |  round
---------------+----------------------+----------+---------
 Mitsubishi    | Galant               | 45861.07 | 4586.11
 Pontiac       | Sunfire              | 20427.86 | 2042.79
 Nissan        | Maxima               | 74412.54 | 7441.25
 Saab          | 900                  | 53428.67 | 5342.87
 Honda         | S2000                | 94521.76 | 9452.18
 GMC           | Jimmy                | 69066.03 | 6906.60
 Ford          | F-Series             | 63790.81 | 6379.08
 GMC           | Sonoma               | 68905.72 | 6890.57
 Ford          | Taurus               | 56825.50 | 5682.55
 Subaru        | Outback              | 34886.05 | 3488.61
 Nissan        | Rogue                | 89159.57 | 8915.96
 Pontiac       | Gemini               | 26245.82 | 2624.58
 Hyundai       | Azera                | 86235.96 | 8623.60
...
...


COALESCE

return the first not null value and that's use for conditionnal sql queries

command :

SELECT COALESCE(45, null, 11, 5, null, null, 12) AS number;
output : 
 number
--------
     45
(1 ligne)

Example : return email from person and replace NULL value with Email not provided

SELECT COALESCE(email, 'Email not provided') from person;
output : 
             coalesce
-----------------------------------
 xsprigging0@paginegialle.it
 aelcoux1@cbsnews.com
 mshillabear2@google.es
 Email not provided
 ofayre4@dyndns.org
 Email not provided
 eerington6@answers.com
 gdaice7@japanpost.jp
 Email not provided
 uvizor9@harvard.edu
 vmcfeea@soup.io
 qtavinorb@google.de
 Email not provided
 kanersend@sciencedaily.com
 aleile@github.io
 dmawmanf@constantcontact.com
...
...


NULLIF

command

SELECT NULLIF(0, 0);

return :

 nullif
--------

(1 ligne)

command

SELECT NULLIF(5, 0);

return

 nullif
--------
5
(1 ligne)

Note : so return the 1st argument if both arguments are different othertwise return NULL



Usecase handling error when divide by 0

When we want divide by 0 we will have trouble because we can't divide by 0 without throw an error but if we divide by null we havent an error and the result will be null so we can handle this usecase by coupling NULLIF with COALESCE

command

SELECT COALESCE(10/ NULLIF(0,0), 0);
output : 
 coalesce
----------
        0
(1 ligne)

command

SELECT COALESCE(10/ NULLIF(5,0), 0);
output : 
 coalesce
----------
        2
(1 ligne)

DATE

The NOW() function returns the current date and time. The return type of the NOW() function is a timestamp with time zone. See the following example:

commande

SELECT NOW();

substraction / add with date :

SELECT NOW() - INTERVAL'10 YEARS';
SELECT NOW() + INTERVAL'10 DAYS';

:: : cast operator so we cast the result of NOW to an other format DATE

SELECT NOW()::DATE;

command : with interval + cast

SELECT (NOW() + INTERVAL'10 MONTHS')::DATE;
output : 
    date
------------
 2022-08-13
(1 ligne)

set a time zone

SET TIMEZONE='America/Los_angeles';


EXTRACT

WORK with date and time only

command :

SELECT EXTRACT(YEAR FROM NOW());

return :

 date_part
-----------
      2021
(1 ligne)


AGE

perform calculation for age

AGE(NOW(), date_of_birth)

command :

SELECT first_name, date_of_birth, AGE(NOW(), date_of_birth) AS age FROM person;
output : 
  first_name  | date_of_birth |               age
--------------+---------------+---------------------------------
 Xylia        | 2020-10-25    | 11 mons 19 days 13:57:40.945903
 Arlette      | 2020-11-02    | 11 mons 11 days 13:57:40.945903
 Maxim        | 2020-12-15    | 9 mons 29 days 13:57:40.945903
 Mannie       | 2021-07-12    | 3 mons 1 day 13:57:40.945903
 Orrin        | 2021-08-29    | 1 mon 15 days 13:57:40.945903
 Heindrick    | 2021-03-13    | 7 mons 13:57:40.945903
 Evelyn       | 2021-09-23    | 20 days 13:57:40.945903
 Gerhard      | 2021-10-11    | 2 days 13:57:40.945903
 Pietro       | 2020-11-02    | 11 mons 11 days 13:57:40.945903
 Udall        | 2021-07-28    | 2 mons 16 days 13:57:40.945903
 Virginie     | 2021-09-19    | 24 days 13:57:40.945903
 Quincey      | 2021-03-14    | 6 mons 30 days 13:57:40.945903
 Duffy        | 2021-01-31    | 8 mons 13 days 13:57:40.945903
 Kissee       | 2020-12-08    | 10 mons 5 days 13:57:40.945903
 Alaric       | 2020-12-09    | 10 mons 4 days 13:57:40.945903
 Doe          | 2020-11-07    | 11 mons 6 days 13:57:40.945903
 Atalanta     | 2021-04-07    | 6 mons 6 days 13:57:40.945903
 Wilfred      | 2021-10-06    | 7 days 13:57:40.945903
...
...

usecase extract month

SELECT first_name, date_of_birth, EXTRACT(MONTH FROM (AGE(NOW(), date_of_birth))) AS age_in_month FROM person;
output : 
  first_name  | date_of_birth | age_in_month
--------------+---------------+-----
 Xylia        | 2020-10-25    |  11
 Arlette      | 2020-11-02    |  11
 Maxim        | 2020-12-15    |   9
 Mannie       | 2021-07-12    |   3
 Orrin        | 2021-08-29    |   1
 Heindrick    | 2021-03-13    |   7
 Evelyn       | 2021-09-23    |   0
 Gerhard      | 2021-10-11    |   0
 Pietro       | 2020-11-02    |  11
 Udall        | 2021-07-28    |   2
 Virginie     | 2021-09-19    |   0
 Quincey      | 2021-03-14    |   6
 Duffy        | 2021-01-31    |   8
...
...


PRIMARY KEYS / FOREIGN KEY & CONSTRAINT

PK for PRIMARY KEY

we can't have a duplicate value for primary key so if we try :

insert into car (id, make, model, price) values (1, 'Mitsubishi', 'Galant', '45861.07');

then we will have an error

ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « car_pkey »
DÉTAIL : La clé « (id)=(1) » existe déjà.

CONCLUSION : primary key add a constraint to the data to be unique


this sequancies of queries :

ALTER TABLE car DROP PRIMARY KEY(id);
insert into car (id, make, model, price) values (1, 'Mitsubishi', 'Galant', '45861.07');
ALTER TABLE car ADD PRIMARY KEY(id);

return :

throw an error because a value is duplicate

so we need to delete first the duplicate person

DELETE FROM person WHERE id = 1;

then after the command for add the primary key constraint will work



CONSTRAINT

ADD

add UNIQUE constraint :

ALTER TABLE person ADD CONSTRAINT unique_email_address UNIQUE (email);

DROP

ALTER TABLE person DROP CONSTRAINT unique_email_address;

After that if we check our table person with the command :

\d person

output :

                                          Table ½ public.person ╗
    Colonne    |          Type          | Collationnement | NULL-able |             Par dÚfaut
---------------+------------------------+-----------------+-----------+------------------------------------
 id            | bigint                 |                 | not null  | nextval('person_id_seq'::regclass)
 first_name    | character varying(50)  |                 |           |
 last_name     | character varying(50)  |                 |           |
 email         | character varying(150) |                 |           |
 gender        | character varying(50)  |                 |           |
 date_of_birth | date                   |                 |           |
 country       | character varying(50)  |                 |           |
Index :
    "person_pkey" PRIMARY KEY, btree (id)
    "unique_email_address" UNIQUE CONSTRAINT, btree (email)

CHECK CONSTRAINT

add a constraint base on a boolean value

to add a condition on the possible values for the gender of a person here only Male or Female so in this case if we try to add a person with a gender that does not match one of the two possibilities then that will throw an error

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


ON Conflict Do Nothing

handle on clonflict scenario

here handle duplicate key error : command :

insert into car (id, make, model, price) values (1, 'Mitsubishi', 'Galant', '45861.07');

return :

ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « car_pkey »
DÉTAIL : La clé « (id)=(1) » existe déjà.

so we will manage the case of conflict which is linked to the id which has a constraint to be unique: and in this case the command becomes :

command :

insert into car (id, make, model, price) values (1, 'Mitsubishi', 'Galant', '45861.07') 
ON CONFLICT (id) DO NOTHING;

return :

INSERT 0 0

Note : INSERT 0 0 mean NO insert was performed because when an insert was properly perform we have INSERT 0 1


command :

insert into car (id, make, model, price) values (1, 'Mitsubishi', 'Galant', '45861.07') 
ON CONFLICT (make) DO NOTHING;

return : will throw an error because no constraint on first_name column value

ERREUR:  il n'existe aucune contrainte unique ou contrainte d'exclusion correspondant à la spécification ON CONFLICT

DO UPDATE instead of DO NOTHING :

  • for example we realize a want to define again a line already existing and rather than DO NOTHING we want to UPDATE the mail of the person of which we make the INSERT with the new value of mail in this case

command :

insert into person (id, first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('1', 'Xylia', 'Sprigging', 'test@test.com', 'Polygender', '2020-10-25', 'Zimbabwe')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;


scenario :

step 1

command :

SELECT * FROM person WHERE id = 1;

return

 id | first_name | last_name |            email            |   gender   | date_of_birth | country_of_birth
----+------------+-----------+-----------------------------+------------+---------------+----------
  1 | Xylia      | Sprigging | xsprigging0@paginegialle.it | Polygender | 2020-10-25    | Zimbabwe
(1 ligne)

step 2

command :

insert into person (id, first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('1', 'Xylia', 'Sprigging', 'test@test.com', 'Polygender', '2020-10-25', 'Zimbabwe')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

return :

INSERT 0 1

step 3

command :

SELECT * FROM person WHERE id = 1;

return :

 id | first_name | last_name |     email     |   gender   | date_of_birth | country_of_birth
----+------------+-----------+---------------+------------+---------------+----------
  1 | Xylia      | Sprigging | test@test.com | Polygender | 2020-10-25    | Zimbabwe
(1 ligne)



FOREIGN KEY

FK is a column that references a PK

ADD a references with UPDATE

command :

UPDATE person SET car_id = 1 WHERE id = 2;

return

UPDATE 1

Now if we try to do it again then :

UPDATE person SET car_id = 1 WHERE id = 2;

return an error because we have an UNIQUE CONSTRAINT


Then if we try to do :

UPDATE person SET car_id = 1003 WHERE id = 2;

return an error because car_id 1003 doesnt exist



JOIN

possibility command result C
JOIN image image
LEFT JOIN image image

command JOIN:

JOIN car ON person.car_id = car.id;
LEFT JOIN car ON person.car_id = car.id;

command LEFT JOIN:

LEFT JOIN car ON person.car_id = car.id;

command :

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


DELETE ROW when we have a FOREIGN KEY

DELETE FROM car WHERE id = 13;

Note : if it's not working because we try to delete a car wich is already referenced by a person. So to be able to delete this car we need to update the person and put the car_id to NULL. So we are sur that the car is not referenced anymore. Then we can delete it



EXPORT to CSV with postrgesql

we want to save the output of the following command inside a CSV file:

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

for that postgresql have already a build in function so we search inside help with :

\?

and finaly we use the following command to do it :

\copy (SELECT * person LEFT JOIN car ON car.id = person.car_id) TO '/path/file.csv' DELIMITER ',' CSV HEADER;


SEQUENCES

ALTER SEQUENCE person_id_seq RESTART WITH 9;


EXTENSION

show available extension

SELECT * FROM pg_available_extensions;

UUID

CREATE TABLE car (
	car_uid UUID NOT NULL PRIMARY KEY,
	make VARCHAR(100) NOT NULL,
	model VARCHAR(100) NOT NULL,
	price NUMERIC(19,2) NOT NULL CHECK (price > 0)
);
CREATE TABLE person (
	person_uid UUID NOT NULL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	email VARCHAR(150),
	gender VARCHAR(50) NOT NULL,
	date_of_birth DATE NOT NULL,
	country_of_birth VARCHAR(50),
    car_uid UUID REFERENCES car(car_uid),
    UNIQUE(car_uid),
    UNIQUE(email)
);

command :

INSERT INTO person(person_uid, first_name, last_name, gender, email, date_of_birth, country of_birth)
VALUES (uuid_generate_v4(), 'John', 'Snow', 'Male', 'jonesnow@gmail.com', '281-10-23', 'Dorne')

<style> blockquote { background-color: lightblue; } </style>

About

Cheat sheet for postgresql and SQL with usefull link

Resources

Readme
Activity

Stars

0 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published