Connect database container
docker exec -it postgres psql -U postgres
[sakhtar@linux docker-compose-postgres]$ docker exec -it postgres psql -U postgres
psql (12.4)
Type "help" for help.
postgres=#
List Database
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
shamim | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
Create Database
postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=#
Drop Database
postgres=# DROP DATABASE test;
CREATE DATABASE
postgres=#
Connect to Database test
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=#
Clear the screeen in posgres prompt
\! clear
**Create Table person **
CREATE TABLE person (
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(7),
date_of_birth DATE
);
List Table
postgres=# \d
public | person | table | postgres
postgres=#
Describe Table
postgres=# \d person;
first_name | character varying(50) | | |
last_name | character varying(50) | | |
gender | character varying(7) | | |
date_of_birth | date | | |
postgres=#
Drop Table
DROP TABLE person;
Create Table with constraints
CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender VARCHAR(5) NOT NULL,
date_of_birth DATE NOT NULL,
email VARCHAR(150)
);
Insert into the table
postgres=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES('Shamim', 'Akhtar', 'Male', '1987-12-02');
postgres=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES('Ali', 'Raza', 'Male', '1984-01-12');
postgres=# INSERT INTO person (first_name, last_name, gender, date_of_birth, email) VALUES('Amir', 'Khan', 'Male', '1986-01-12', '[email protected]');
postgres=# select * from person;
1 | Shamim | Akhtar | Male | 1987-12-02 |
2 | Ali | Raza | Male | 1984-01-12 |
3 | Amir | Khan | Male | 1986-01-12 | [email protected]
postgres=#
Create mock data
Create mock data from here as given below and download person.sql
How to populate sql data to postgres docker container
Find the name and id of the Docker container hosting the Postgres instance
[sakhtar@linux ~]$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
91865223f3e7 dpage/pgadmin4:4.24 "/entrypoint.sh" 19 seconds ago Up 17 seconds 443/tcp, 0.0.0.0:8080->80/tcp pgadmin
c4a425bb92c1 postgres:12.4-alpine "docker-entrypoint.s…" 19 seconds ago Up 17 seconds 0.0.0.0:5432->5432/tcp postgres
Find the volumes available in the Docker container
docker inspect -f '{{ json .Mounts }}' <container_id> | python -m json.tool
[sakhtar@linux ~]$ docker inspect -f '{{ json .Mounts }}' c4a425bb92c1 | python -m json.tool
[
{
"Destination": "/var/lib/postgresql/data",
"Driver": "local",
"Mode": "rw",
"Name": "docker-compose-postgres_postgres",
"Propagation": "",
"RW": true,
"Source": "/var/lib/docker/volumes/docker-compose-postgres_postgres/_data",
"Type": "volume"
}
]
[sakhtar@linux ~]$
In this case, we have /var/lib/postgresql/data as the volume path.
Copy dump into one of the volumes
docker cp ./SQL/person.sql postgres:/var/lib/postgresql/data/
Now populate sql from postgres cli
postgres=# \i /var/lib/postgresql/data/person.sql;
SQL Select From
Order by
Distinct
Where Clause and AND
Comparison Operators
Limit, Offset & Fetch
IN
Between
test=# SELECT * FROM person WHERE date_of_birth BETWEEN DATE '2019-01-01' AND '2019-12-31';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+------------+-------------+-----------------------------------+--------+---------------+------------------
131 | Marybeth | Coverley | [email protected] | Female | 2019-02-28 | Philippines
164 | Laurianne | Ferier | [email protected] | Female | 2019-02-17 | Portugal
251 | Catriona | Tatershall | [email protected] | Female | 2019-11-19 | Russia
274 | Concettina | Nan Carrow | [email protected] | Female | 2019-06-30 | China
296 | Marv | MacCathay | [email protected] | Male | 2019-02-01 | Afghanistan
316 | Eleanor | Wasielewicz | [email protected] | Female | 2019-07-08 | Indonesia
423 | Gabe | Tollerfield | [email protected] | Male | 2019-10-14 | Russia
543 | Idelle | Burgane | [email protected] | Female | 2019-06-05 | Vietnam
579 | Rogerio | Daine | [email protected] | Male | 2019-04-23 | Philippines
638 | Allayne | Rowlstone | [email protected] | Male | 2019-04-03 | Thailand
710 | Ramona | Kidstone | [email protected] | Female | 2019-04-14 | South Africa
762 | Birch | Tebb | [email protected] | Male | 2019-09-16 | Indonesia
826 | Darcie | Verdie | [email protected] | Female | 2019-01-25 | Switzerland
887 | Kerk | Geraldini | [email protected] | Male | 2019-10-29 | Ukraine
968 | Brande | Goulbourne | [email protected] | Female | 2019-12-06 | Indonesia
(15 rows)
Like
test=# SELECT * FROM person WHERE email LIKE '%xing.com';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+------------+-----------+-----------------------+--------+---------------+------------------
41 | Jere | Bosward | [email protected] | Male | 1981-11-22 | Poland
646 | Bethanne | Patek | [email protected] | Female | 2010-05-09 | United States
668 | Tine | Carbonell | [email protected] | Female | 2005-09-14 | Poland
798 | Valry | Smither | [email protected] | Female | 1950-01-06 | Indonesia
863 | Gabby | Sharpe | [email protected] | Male | 1951-06-16 | Norway
(5 rows)
test=#
test=# SELECT * FROM person WHERE email LIKE '%@google.com';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+------------+-----------+-----------------------+--------+---------------+------------------
284 | Jasmine | Minshaw | [email protected] | Female | 2013-05-15 | China
(1 row)
test=# SELECT * FROM person WHERE email LIKE '%@google.%';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+--------------+----------------+---------------------------------+--------+---------------+--------------------
10 | Charmian | Lambell | [email protected] | Female | 1992-12-31 | Ukraine
17 | Galen | Wankling | [email protected] | Male | 1950-08-12 | China
100 | Leroi | Leivers | [email protected] | Male | 2003-08-10 | Malaysia
105 | Marie-jeanne | Kopp | [email protected] | Female | 1948-09-01 | Indonesia
146 | Derby | Biddle | [email protected] | Male | 1999-02-20 | Indonesia
176 | Danyette | Linskey | [email protected] | Female | 2005-01-09 | South Korea
202 | Clary | Brawley | [email protected] | Female | 2004-09-17 | China
230 | Zahara | Burchett | [email protected] | Female | 1977-08-20 | China
263 | Deeyn | Thackray | [email protected] | Female | 1969-08-13 | Sweden
284 | Jasmine | Minshaw | [email protected] | Female | 2013-05-15 | China
287 | Sidnee | Paulou | [email protected] | Male | 1994-06-27 | China
321 | Joceline | Baine | [email protected] | Female | 1956-01-28 | Argentina
330 | Conrado | Leeke | [email protected] | Male | 2020-05-25 | Indonesia
387 | Eduino | Poundsford | [email protected] | Male | 1970-03-25 | Philippines
427 | George | Lune | [email protected] | Male | 1988-02-24 | Chile
470 | Poppy | Whiskerd | [email protected] | Female | 1975-04-22 | United States
584 | Sergei | Mustin | [email protected] | Male | 1997-02-05 | Ukraine
593 | Sinclare | Jimson | [email protected] | Male | 1995-12-26 | Indonesia
610 | Phillie | Fearnill | [email protected] | Female | 1976-10-09 | China
612 | Danika | aManger | [email protected] | Female | 2000-01-26 | United States
669 | Budd | Rodger | [email protected] | Male | 1973-07-09 | Greece
678 | Leah | Lidyard | [email protected] | Female | 2020-01-27 | Vietnam
694 | Vassily | Alelsandrovich | [email protected] | Male | 1979-07-04 | Sweden
695 | Mendie | Cambden | [email protected] | Male | 2020-04-12 | Honduras
728 | Archambault | Demcik | [email protected] | Male | 2010-11-11 | Brazil
732 | Belia | Nortcliffe | [email protected] | Female | 2005-08-04 | Dominican Republic
742 | Meier | Girke | [email protected] | Male | 1948-10-07 | Portugal
752 | Darrell | Ciobutaro | [email protected] | Male | 1973-07-07 | China
916 | Mallissa | Mangan | [email protected] | Female | 1997-11-14 | Poland
936 | Francoise | Filchakov | [email protected] | Female | 2006-01-16 | Azerbaijan
942 | Herc | Ruit | [email protected] | Male | 2005-02-18 | Serbia
967 | Hatty | Martynka | [email protected] | Female | 2002-04-24 | Bahrain
979 | Goraud | Vaudrey | [email protected] | Male | 1958-04-25 | Canada
981 | Ogdon | Bosley | [email protected] | Male | 1949-04-11 | France
(34 rows)
test=#
Single character with underscore (_)
test=# SELECT * FROM person WHERE email LIKE '______@%';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+------------+-----------+----------------------------+--------+---------------+------------------
15 | Armando | Hume | [email protected] | Male | 2001-04-30 | Ukraine
30 | Lorens | Kirk | [email protected] | Male | 2013-10-15 | Indonesia
239 | Jerrine | Say | [email protected] | Female | 1965-08-12 | United States
261 | Gerardo | Axe | [email protected] | Male | 2015-05-10 | Kuwait
295 | Carmel | Esh | [email protected] | Female | 1959-12-02 | Sweden
341 | Enos | Odo | [email protected] | Male | 1968-04-11 | Russia
436 | Emiline | Yon | [email protected] | Female | 2003-10-15 | Sweden
693 | Noami | Raw | [email protected] | Female | 1979-12-30 | Luxembourg
823 | Chan | Mix | [email protected] | Male | 1949-03-21 | Indonesia
868 | Leanora | Roe | [email protected] | Female | 1951-01-21 | Poland
975 | Elisabet | Dix | [email protected] | Female | 1965-04-04 | Brazil
(11 rows)
test=#
iLike
test=# SELECT * FROM person WHERE country_of_birth LIKE 'a%';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+-----------+-------+--------+---------------+------------------
(0 rows)
test=# SELECT * FROM person WHERE country_of_birth ILIKE 'a%';
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+------------+------------+--------------------------------+--------+---------------+------------------
5 | Isaiah | Titmuss | [email protected] | Male | 2015-04-25 | Albania
26 | Jacquelyn | Mowat | [email protected] | Female | 1960-07-23 | Albania
34 | Thaine | Oades | [email protected] | Male | 1964-03-04 | Argentina
49 | Lorita | Allcoat | [email protected] | Female | 1998-04-02 | Argentina
81 | Minni | Roocroft | [email protected] | Female | 2004-12-17 | Armenia
85 | Lalo | Alden | [email protected] | Male | 2007-07-13 | Argentina
214 | Ramon | Grafom | [email protected] | Male | 2005-01-26 | Argentina
255 | Effie | Kluss | [email protected] | Female | 2005-04-01 | Angola
291 | John | Reace | [email protected] | Male | 1960-09-13 | Albania
296 | Marv | MacCathay | [email protected] | Male | 2019-02-01 | Afghanistan
297 | Burty | Bester | [email protected] | Male | 1953-11-21 | Argentina
304 | Charo | Loxdale | [email protected] | Female | 1997-12-08 | Argentina
321 | Joceline | Baine | [email protected] | Female | 1956-01-28 | Argentina
384 | Obidiah | Dallemore | [email protected] | Male | 2005-04-27 | Afghanistan
521 | Wallis | Gravenell | [email protected] | Female | 1965-11-02 | Afghanistan
550 | Herbie | Gummie | [email protected] | Male | 2013-10-04 | Argentina
553 | Chev | Rutigliano | [email protected] | Male | 1964-05-07 | Argentina
664 | Dionne | Costy | [email protected] | Female | 1960-09-24 | Argentina
670 | Cobb | Rawlin | [email protected] | Male | 1992-02-21 | Armenia
714 | Franni | Proger | [email protected] | Female | 1949-08-07 | Afghanistan
736 | Shirline | Chaloner | [email protected] | Female | 1999-02-02 | Azerbaijan
763 | Egor | Clemes | [email protected] | Male | 1999-08-03 | Argentina
816 | Kennett | Lembrick | [email protected] | Male | 1982-11-18 | Afghanistan
878 | Dorey | Hunnicot | [email protected] | Female | 1948-12-18 | Argentina
883 | Alikee | Itzakson | [email protected] | Female | 2016-11-21 | Argentina
886 | Crichton | Girth | [email protected] | Male | 1989-08-14 | Argentina
905 | Taddeo | Staveley | [email protected] | Male | 1994-10-25 | Afghanistan
930 | Lindon | Goldie | [email protected] | Male | 1982-06-04 | Afghanistan
936 | Francoise | Filchakov | [email protected] | Female | 2006-01-16 | Azerbaijan
938 | Cecilia | Bambury | [email protected] | Female | 2006-05-12 | Argentina
952 | Silvano | Commuzzo | [email protected] | Male | 1995-03-28 | Argentina
970 | Andres | Rumbold | [email protected] | Male | 1978-09-14 | Armenia
983 | Mina | Alcorn | [email protected] | Female | 2001-08-31 | Armenia
(33 rows)
test=#
Group By
test=# SELECT country_of_birth, count(*) FROM person GROUP BY country_of_birth;
country_of_birth | count
----------------------------------+-------
Bangladesh | 3
Indonesia | 115
Venezuela | 7
Luxembourg | 3
Czech Republic | 14
Sweden | 29
Uganda | 5
Jordan | 2
Dominican Republic | 4
Ireland | 3
Macedonia | 1
Papua New Guinea | 1
Sri Lanka | 1
Uzbekistan | 5
Finland | 2
Portugal | 37
Malta | 1
Colombia | 19
Albania | 3
Saudi Arabia | 3
Ukraine | 17
Cuba | 4
Latvia | 3
North Korea | 1
Kyrgyzstan | 2
France | 25
Cape Verde | 1
Maldives | 1
Israel | 4
Kenya | 1
Ghana | 1
Senegal | 1
Malaysia | 5
Zambia | 1
Madagascar | 2
Kuwait | 1
Sierra Leone | 1
Bosnia and Herzegovina | 7
Philippines | 50
Benin | 1
United States | 25
Guinea | 1
Nigeria | 5
Tajikistan | 1
Comoros | 1
China | 163
New Caledonia | 2
Belarus | 3
Armenia | 4
Netherlands | 1
Serbia | 5
Mauritius | 5
Angola | 1
Bahrain | 1
Vanuatu | 1
Spain | 3
Georgia | 1
Malawi | 2
Belgium | 1
Democratic Republic of the Congo | 3
Bhutan | 1
Thailand | 10
El Salvador | 1
United Kingdom | 6
Germany | 2
Canada | 10
South Korea | 7
Argentina | 16
Azerbaijan | 2
Slovenia | 2
Egypt | 2
Greece | 13
Afghanistan | 7
Chad | 1
Iran | 2
Chile | 7
Gambia | 1
Estonia | 1
Vietnam | 9
South Africa | 7
Peru | 22
Kazakhstan | 2
Japan | 15
Ivory Coast | 1
Denmark | 1
Mongolia | 3
Mauritania | 1
Switzerland | 1
Ecuador | 2
New Zealand | 4
Hungary | 3
Russia | 67
Norway | 4
Honduras | 5
Pakistan | 6
Brazil | 41
Guatemala | 2
Guinea-Bissau | 1
Bolivia | 3
Ethiopia | 2
Niger | 1
Panama | 2
Yemen | 4
Bulgaria | 3
Turkmenistan | 1
Croatia | 3
Tunisia | 3
Sudan | 3
Morocco | 1
Moldova | 3
Myanmar | 3
Nicaragua | 2
Mexico | 8
Tanzania | 5
Palestinian Territory | 7
Poland | 39
Costa Rica | 2
Haiti | 1
(118 rows)
Group By Having
test=# SELECT country_of_birth, count(*) FROM person GROUP BY country_of_birth ORDER BY country_of_birth;
country_of_birth | count
----------------------------------+-------
Afghanistan | 7
Albania | 3
Angola | 1
Argentina | 16
Armenia | 4
Azerbaijan | 2
Bahrain | 1
Bangladesh | 3
Belarus | 3
Belgium | 1
Benin | 1
Bhutan | 1
Bolivia | 3
Bosnia and Herzegovina | 7
Brazil | 41
Bulgaria | 3
Canada | 10
Cape Verde | 1
Chad | 1
Chile | 7
China | 163
Colombia | 19
Comoros | 1
Costa Rica | 2
Croatia | 3
Cuba | 4
Czech Republic | 14
Democratic Republic of the Congo | 3
Denmark | 1
Dominican Republic | 4
Ecuador | 2
Egypt | 2
El Salvador | 1
Estonia | 1
Ethiopia | 2
Finland | 2
France | 25
Gambia | 1
Georgia | 1
Germany | 2
Ghana | 1
Greece | 13
Guatemala | 2
Guinea | 1
Guinea-Bissau | 1
Haiti | 1
Honduras | 5
Hungary | 3
Indonesia | 115
Iran | 2
Ireland | 3
Israel | 4
Ivory Coast | 1
Japan | 15
Jordan | 2
Kazakhstan | 2
Kenya | 1
Kuwait | 1
Kyrgyzstan | 2
Latvia | 3
Luxembourg | 3
Macedonia | 1
Madagascar | 2
Malawi | 2
Malaysia | 5
Maldives | 1
Malta | 1
Mauritania | 1
Mauritius | 5
Mexico | 8
Moldova | 3
Mongolia | 3
Morocco | 1
Myanmar | 3
Netherlands | 1
New Caledonia | 2
New Zealand | 4
Nicaragua | 2
Niger | 1
Nigeria | 5
North Korea | 1
Norway | 4
Pakistan | 6
Palestinian Territory | 7
Panama | 2
Papua New Guinea | 1
Peru | 22
Philippines | 50
Poland | 39
Portugal | 37
Russia | 67
Saudi Arabia | 3
Senegal | 1
Serbia | 5
Sierra Leone | 1
Slovenia | 2
South Africa | 7
South Korea | 7
Spain | 3
Sri Lanka | 1
Sudan | 3
Sweden | 29
Switzerland | 1
Tajikistan | 1
Tanzania | 5
Thailand | 10
Tunisia | 3
Turkmenistan | 1
Uganda | 5
Ukraine | 17
United Kingdom | 6
United States | 25
Uzbekistan | 5
Vanuatu | 1
Venezuela | 7
Vietnam | 9
Yemen | 4
Zambia | 1
(118 rows)
Order by and having
test=# SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth HAVING COUNT(*) > 5 ORDER BY country_of_birth;
country_of_birth | count
------------------------+-------
Afghanistan | 7
Argentina | 16
Bosnia and Herzegovina | 7
Brazil | 41
Canada | 10
Chile | 7
China | 163
Colombia | 19
Czech Republic | 14
France | 25
Greece | 13
Indonesia | 115
Japan | 15
Mexico | 8
Pakistan | 6
Palestinian Territory | 7
Peru | 22
Philippines | 50
Poland | 39
Portugal | 37
Russia | 67
South Africa | 7
South Korea | 7
Sweden | 29
Thailand | 10
Ukraine | 17
United Kingdom | 6
United States | 25
Venezuela | 7
Vietnam | 9
(30 rows)
Adding New Table And Data Using Mockaroo
docker cp ./SQL/car.sql postgres:/var/lib/postgresql/data/
test=#\i /var/lib/postgresql/data/car.sql;
Calculating Min, Max & Average
test=# SELECT MAX(price) FROM car;
max
----------
99977.77
(1 row)
test=# SELECT MIN(price) FROM car;
min
----------
10004.02
(1 row)
test=# SELECT AVG(price) FROM car;
avg
--------------------
54749.887760000000
(1 row)
test=# SELECT ROUND(AVG(price)) FROM car;
round
-------
54750
(1 row)
test=#
test=# SELECT make,model, MIN(price) FROM car GROUP BY make,model limit 5;
make | model | min
---------------+--------------+----------
Dodge | Dynasty | 20122.52
Pontiac | GTO | 13474.44
Toyota | Land Cruiser | 24105.78
Mitsubishi | Sigma | 56004.23
Mercedes-Benz | S-Class | 12745.91
(5 rows)
test=#
SELECT make,model, MAX(price) FROM car GROUP BY make,model limit 5;
make | model | max
---------------+--------------+----------
Dodge | Dynasty | 20122.52
Pontiac | GTO | 84281.94
Toyota | Land Cruiser | 98970.85
Mitsubishi | Sigma | 56004.23
Mercedes-Benz | S-Class | 88909.14
(5 rows)
SELECT make, MAX(price) FROM car GROUP BY make limit 5;
make | max
----------+----------
Ford | 99333.97
Smart | 11531.92
Maserati | 87641.73
Dodge | 98636.05
Infiniti | 95521.32
(5 rows)
Sum
test=# SELECT make,SUM(price) FROM car GROUP BY make limit 5;
make | sum
----------+------------
Ford | 5465482.91
Smart | 11531.92
Maserati | 182099.15
Dodge | 2515519.58
Infiniti | 966709.93
(5 rows)
Basics of Arithmetic Operators
test=# SELECT id,make,model,price, ROUND(price * .10,2) from car limit 5;
id | make | model | price | round
----+-----------+---------------+----------+---------
1 | Honda | CR-V | 54286.10 | 5428.61
2 | Maybach | 57 | 74847.63 | 7484.76
3 | Chevrolet | Suburban 1500 | 62678.03 | 6267.80
4 | Volvo | S80 | 32832.14 | 3283.21
5 | Hyundai | Sonata | 39201.07 | 3920.11
(5 rows)
Arithmetic Operators (ROUND)
test=# SELECT id,make,model,price, ROUND(price * .10,2) AS "10% of price", ROUND(price - (price * .10),2) AS "Rest Amt after 10%" from car limit 5;
id | make | model | price | 10% of price | Rest Amt after 10%
----+-----------+---------------+----------+--------------+--------------------
1 | Honda | CR-V | 54286.10 | 5428.61 | 48857.49
2 | Maybach | 57 | 74847.63 | 7484.76 | 67362.87
3 | Chevrolet | Suburban 1500 | 62678.03 | 6267.80 | 56410.23
4 | Volvo | S80 | 32832.14 | 3283.21 | 29548.93
5 | Hyundai | Sonata | 39201.07 | 3920.11 | 35280.96
(5 rows)
Alias
test=# SELECT id AS "ID",make AS "MAKE",model AS "MODEL",price AS "PRICE", ROUND(price * .10,2) AS "10% Discount", ROUND(price - (price * .10),2) AS "Price after Discount" from car limit 5;
ID | MAKE | MODEL | PRICE | 10% Discount | Price after Discount
----+-----------+---------------+----------+--------------+----------------------
1 | Honda | CR-V | 54286.10 | 5428.61 | 48857.49
2 | Maybach | 57 | 74847.63 | 7484.76 | 67362.87
3 | Chevrolet | Suburban 1500 | 62678.03 | 6267.80 | 56410.23
4 | Volvo | S80 | 32832.14 | 3283.21 | 29548.93
5 | Hyundai | Sonata | 39201.07 | 3920.11 | 35280.96
(5 rows
Coalesce
test=# SELECT COALESCE(email,'Email not provided')FROM person;
coalesce
--------------------------------------
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Email not provided
[email protected]
[email protected]
Email not provided
[email protected]
Email not provided
[email protected]
[email protected]
Email not provided
[email protected]
Email not provided
Email not provided
Email not provided
[email protected]
[email protected]
NULLIF
test=# SELECT COALESCE(10 / NULLIF(0,0),0);
coalesce
----------
0
(1 row)
Timestamps And Dates Course
test=# SELECT NOW();
now
-------------------------------
2020-08-22 10:47:28.137981+00
(1 row)
test=# SELECT NOW()::DATE;
now
------------
2020-08-22
(1 row)
test=# SELECT NOW()::TIME;
now
-----------------
10:48:04.193382
(1 row)
Adding And Subtracting With Dates
test=# SELECT NOW();
now
-------------------------------
2020-08-22 10:49:46.718122+00
(1 row)
test=# SELECT NOW() - INTERVAL '1 YEAR';
?column?
-------------------------------
2019-08-22 10:50:14.951412+00
(1 row)
test=# SELECT NOW() - INTERVAL '10 YEAR';
?column?
-------------------------------
2010-08-22 10:50:27.188566+00
(1 row)
test=#
test=# SELECT NOW() - INTERVAL '7 MONTHS';
?column?
-------------------------------
2020-01-22 10:51:18.430986+00
(1 row)
test=# SELECT NOW() - INTERVAL '2 DAYS';
?column?
-------------------------------
2020-08-20 10:51:35.083528+00
(1 row)
Extracting Fields From Timestamp
test=# SELECT EXTRACT(YEAR FROM NOW());
date_part
-----------
2020
(1 row)
test=# SELECT EXTRACT(MONTH FROM NOW());
date_part
-----------
8
(1 row)
test=# SELECT EXTRACT(DAY FROM NOW());
date_part
-----------
22
(1 row)
test=# SELECT EXTRACT(DOW FROM NOW());
date_part
-----------
6
(1 row)
test=#
Age Function
test=# SELECT first_name, last_name, gender, country_of_birth, date_of_birth, AGE(NOW(),date_of_birth) FROM person LIMIT 5;
first_name | last_name | gender | country_of_birth | date_of_birth | age
------------+-----------+--------+------------------+---------------+-----------------------------------------
Feliks | Prandin | Male | Indonesia | 2012-10-18 | 7 years 10 mons 4 days 11:11:37.50254
Gabbie | Scrase | Female | China | 1965-06-02 | 55 years 2 mons 20 days 11:11:37.50254
Alexandra | Ashbe | Female | Ukraine | 1993-12-06 | 26 years 8 mons 16 days 11:11:37.50254
Cheston | Eustanch | Male | Sweden | 1959-10-04 | 60 years 10 mons 18 days 11:11:37.50254
Ruthe | McPartlin | Female | China | 1998-02-25 | 22 years 5 mons 25 days 11:11:37.50254
(5 rows)
What Are Primary Keys
Understanding Primary Keys
DROP Primary Key constrints
ALTER TABLE person DROP CONSTRAINT person_pkey;
Adding Primary Key
ALTER TABLE person ADD PRIMARY KEY(id);
Unique Constraints
test=# ALTER TABLE person ADD CONSTRAINT unique_email_address UNIQUE(email);
test=#
test=# \d person;
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
first_name | character varying(50) | | not null |
last_name | character varying(50) | | not null |
email | character varying(150) | | |
gender | character varying(7) | | not null |
date_of_birth | date | | not null |
country_of_birth | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"unique_email_address" UNIQUE CONSTRAINT, btree (email)
or
test=# ALTER TABLE person ADD UNIQUE(email);
ALTER TABLE
test=# \d person;
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
first_name | character varying(50) | | not null |
last_name | character varying(50) | | not null |
email | character varying(150) | | |
gender | character varying(7) | | not null |
date_of_birth | date | | not null |
country_of_birth | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"person_email_key" UNIQUE CONSTRAINT, btree (email)
Check Constraints
ALTER TABLE person ADD CONSTRAINT gender_constrint or CHECK (gender = 'Female' OR gender = 'Male');
OR
test=# ALTER TABLE person ADD CHECK (gender = 'Female' OR gender = 'Male');
ALTER TABLE
test=# \d person;
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
first_name | character varying(50) | | not null |
last_name | character varying(50) | | not null |
email | character varying(150) | | |
gender | character varying(7) | | not null |
date_of_birth | date | | not null |
country_of_birth | character varying(50) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"person_email_key" UNIQUE CONSTRAINT, btree (email)
Check constraints:
"person_gender_check" CHECK (gender::text = 'Female'::text OR gender::text = 'Male'::text)
How to Delete Records
test=# DELETE FROM person WHERE ID = 2;
test=# SELECT * FROM person LIMIT 10;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+------------+--------------------------------+--------+---------------+------------------
1 | Feliks | Prandin | [email protected] | Male | 2012-10-18 | Indonesia
3 | Alexandra | Ashbe | [email protected] | Female | 1993-12-06 | Ukraine
4 | Cheston | Eustanch | [email protected] | Male | 1959-10-04 | Sweden
5 | Ruthe | McPartlin | [email protected] | Female | 1998-02-25 | China
6 | Katerina | Peeters | [email protected] | Female | 1979-05-10 | Brazil
7 | Walden | Leirmonth | | Male | 2013-08-24 | Argentina
8 | Donelle | Franscioni | [email protected] | Female | 1967-01-26 | Pakistan
9 | Curtis | Dumelow | [email protected] | Male | 2010-04-05 | Germany
10 | Hana | Riggert | | Female | 1954-07-02 | Ukraine
11 | Dur | Freiberg | [email protected] | Male | 2012-10-08 | United States
(10 rows)
test=# DELETE FROM person WHERE gender = 'Male';
DELETE 535
How to Update Records
test=# select * from person limit 1;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+-----------+-----------------------------+--------+---------------+------------------
3 | Alexandra | Ashbe | [email protected] | Female | 1993-12-06 | Ukraine
(1 row)
test=#
test=# select * from person WHERE country_of_birth = 'Pakistan' limit 1;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+------------+---------------------------+--------+---------------+------------------
8 | Donelle | Franscioni | [email protected] | Female | 1967-01-26 | Pakistan
(1 row)
test=#
test=# UPDATE person SET first_name = 'Jahir', last_name = 'Khan' Where id = 8 ;
UPDATE 1
test=# SELECT * FROM person WHERE id = 8;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+-----------+---------------------------+--------+---------------+------------------
8 | Jahir | Khan | [email protected] | Female | 1967-01-26 | Pakistan
(1 row)
On Conflict Do Nothing
test=# insert into person (id, first_name, last_name, email, gender, date_of_birth, country_of_birth) values (10, 'Hana', 'Riggert', null, 'Female', '7/2/1954', 'Ukraine');
ERROR: duplicate key value violates unique constraint "person_pkey"
DETAIL: Key (id)=(10) already exists.
test=#
test=#
test=# insert into person (id, first_name, last_name, email, gender, date_of_birth, country_of_birth) values (10, 'Hana', 'Riggert', null, 'Female', '7/2/1954', 'Ukraine') ON CONFLICT (id) DO NOTHING;
INSERT 0 0
test=#
Upsert
test=# select * from person where id = 10;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+-----------+-------+--------+---------------+------------------
10 | Hana | Riggert | | Female | 1954-07-02 | Ukraine
(1 row)
test=# insert into person (id, first_name, last_name, email, gender, date_of_birth, country_of_birth) values (10, 'Hana', 'Riggert', null, 'Female', '7/2/1954', 'Ukraine') ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;
INSERT 0 1
test=# select * from person where id = 10;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+-----------+-------+--------+---------------+------------------
10 | Hana | Riggert | | Female | 1954-07-02 | Ukraine
(1 row)
test=#
test=# insert into person (id, first_name, last_name, email, gender, date_of_birth, country_of_birth) values (10, 'Hana', 'Riggert', '[email protected]', 'Female', '7/2/1954', 'Ukraine') ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;
INSERT 0 1
test=# select * from person where id = 10;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
----+------------+-----------+-------------------------+--------+---------------+------------------
10 | Hana | Riggert | [email protected] | Female | 1954-07-02 | Ukraine
(1 row)
What Is A Relationship/Foreign Keys
A foreign is a referencd to primary key in another table.
Adding Relationship Between Tables
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
);
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(7) NOT NULL,
date_of_birth DATE NOT NULL,
country_of_birth VARCHAR(50),
car_id BIGINT REFERENCES car (id),
UNIQUE(car_id)
);
insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Feliks', 'Prandin', '[email protected]', 'Male', '10/18/2012', 'Indonesia');
insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Gabbie', 'Scrase', null, 'Female', '6/2/1965', 'China');
insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Alexandra', 'Ashbe', '[email protected]', 'Female', '12/6/1993', 'Ukraine');
insert into car (make, model, price) values ('Honda', 'CR-V', '54286.10');
insert into car (make, model, price) values ('Maybach', '57', '74847.63');
test=# select * from car;
id | make | model | price
----+---------+-------+----------
1 | Honda | CR-V | 54286.10
2 | Maybach | 57 | 74847.63
(2 rows)
test=# select * from person;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth | car_id
----+------------+-----------+-----------------------------+--------+---------------+------------------+--------
1 | Feliks | Prandin | [email protected] | Male | 2012-10-18 | Indonesia |
2 | Gabbie | Scrase | | Female | 1965-06-02 | China |
3 | Alexandra | Ashbe | [email protected] | Female | 1993-12-06 | Ukraine |
(3 rows)
test=#
Updating Foreign Keys Columns
test=# select * from car;
id | make | model | price
----+---------+-------+----------
1 | Honda | CR-V | 54286.10
2 | Maybach | 57 | 74847.63
(2 rows)
test=# select * from person;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth | car_id
----+------------+-----------+-----------------------------+--------+---------------+------------------+--------
1 | Feliks | Prandin | [email protected] | Male | 2012-10-18 | Indonesia |
2 | Gabbie | Scrase | | Female | 1965-06-02 | China |
3 | Alexandra | Ashbe | [email protected] | Female | 1993-12-06 | Ukraine |
(3 rows)
test=# UPDATE person SET car_id = 2 WHERE id = 1;
UPDATE 1
test=# SELECT * FROM person;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth | car_id
----+------------+-----------+-----------------------------+--------+---------------+------------------+--------
2 | Gabbie | Scrase | | Female | 1965-06-02 | China |
3 | Alexandra | Ashbe | [email protected] | Female | 1993-12-06 | Ukraine |
1 | Feliks | Prandin | [email protected] | Male | 2012-10-18 | Indonesia | 2
(3 rows)
test=# UPDATE person SET car_id = 2 WHERE id = 2;
ERROR: duplicate key value violates unique constraint "person_car_id_key"
DETAIL: Key (car_id)=(2) already exists.
test=# UPDATE person SET car_id = 1 WHERE id = 2;
UPDATE 1
test=# SELECT * FROM person;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth | car_id
----+------------+-----------+-----------------------------+--------+---------------+------------------+--------
3 | Alexandra | Ashbe | [email protected] | Female | 1993-12-06 | Ukraine |
1 | Feliks | Prandin | [email protected] | Male | 2012-10-18 | Indonesia | 2
2 | Gabbie | Scrase | | Female | 1965-06-02 | China | 1
(3 rows)
**Expanded display **
test=# \x
Expanded display is on.
or
test=# \x auto;
Expanded display is used automatically.
test=#
test=# SELECT * FROM person
JOIN car ON person.car_id = car.id;
-[ RECORD 1 ]----+-----------------------
id | 2
first_name | Gabbie
last_name | Scrase
email |
gender | Female
date_of_birth | 1965-06-02
country_of_birth | China
car_id | 1
id | 1
make | Honda
model | CR-V
price | 54286.10
-[ RECORD 2 ]----+-----------------------
id | 1
first_name | Feliks
last_name | Prandin
email | [email protected]
gender | Male
date_of_birth | 2012-10-18
country_of_birth | Indonesia
car_id | 2
id | 2
make | Maybach
model | 57
price | 74847.63
Inner Joins
test=# SELECT person.first_name, car.make, car.model, car.price
FROM person
JOIN car ON person.car_id = car.id;
first_name | make | model | price
------------+---------+-------+----------
Gabbie | Honda | CR-V | 54286.10
Feliks | Maybach | 57 | 74847.63
(2 rows)
test=#
Left Joins
test=# SELECT * FROM person
test-# LEFT JOIN car ON car.id = person.car_id;
id | first_name | last_name | email | gender | date_of_birth | country_of_birth | car_id | id | make | model | price
----+------------+-----------+-----------------------------+--------+---------------+------------------+--------+----+---------+-------+----------
2 | Gabbie | Scrase | | Female | 1965-06-02 | China | 1 | 1 | Honda | CR-V | 54286.10
1 | Feliks | Prandin | [email protected] | Male | 2012-10-18 | Indonesia | 2 | 2 | Maybach | 57 | 74847.63
3 | Alexandra | Ashbe | [email protected] | Female | 1993-12-06 | Ukraine | | | | |
(3 rows)
Deleting Records With Foreign Keys
Exporting Query Results to CSV
Serial & Sequences
Extensions
Understanding UUID Data Type
UUID As Primary Keys