1
2
3
4
5
6
7
8
9
10
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) NOT NULL
);
-- More lines containing INSERT command, We are not showing them here.
Professional writings
1.7. Another Table Called
comments powered by Disqus
PostgreSQL: The Surface Sea
This writing is actually a draft note made for me so that I myself don’t forget what I have learned. For that, I think this note will be helpful for those who want to learn new Postgres.
1. The Surface Sea
1.1. Mockaroo Data Generator
We will use a site named Mockaroo to insert a lot of data into our table for our learning convenience. Mockaroo is an online realist test data generator. We will download a bunch of dummy but realistic data in SQL format and execute the SQL file in the terminal.
Notes:
-
Field Names and types in Mockaroo according to the image above.
-
For our learning convenience, make sure 30% blank in the
emailfield. -
Set a acceptable nice range for data of birth.
-
To find the type of each field, we have to search with the appropriate keyword.
-
Tick the include create table option.
Download the data as a file named person.sql.
Now we will do some tweaking in person.sql, according to our needs. Open this file in the preferred editor, I’m using vs code. Then make the following changes to the CREATE TABLE command at the top of the file. Notice that we have added id BIGSERIAL NOT NULL PRIMARY KEY, changed VARCHAR sizes, and specified the NOT NULL fields.
After saving our changed person.sql file, now we will execute it. We can copy the whole file and paste it into Postgres terminal, that will work too, but we are going to do it in a better way. \i executes a script file of SQL commands.
test=# \i /path/to/person.sqlNow the script person.sql is executed, and there are 1000 rows in the person table.
Our table description look like this:
1
2
3
4
5
6
7
8
9
10
test=# \d person;
Table "public.person"
Column | Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+---------
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) | | not null |
Here we have made a query to fetch all the data from the person table.
Command
SELECT * FROM person;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+----------------+---------------------+-----------------------------------------+--------+---------------+----------------------------------
1 | Ronda | Skermer | rskermer0@arstechnica.com | Female | 1993-06-30 | Argentina
2 | Hamid | Abbett | habbett1@cbc.ca | Male | 1995-08-31 | Ethiopia
3 | Francis | Nickerson | fnickerson2@mac.com | Male | 1998-03-16 | Portugal
4 | Erminie | M'Quharg | emquharg3@e-recht24.de | Female | 1999-03-13 | Mozambique
5 | Teodoro | Trimmill | | Male | 1982-04-30 | China
6 | Reilly | Amesbury | ramesbury5@businessinsider.com | Male | 1990-12-31 | China
7 | West | Elphey | | Male | 2004-03-29 | Indonesia
8 | Letta | Caurah | lcaurah7@yale.edu | Female | 1994-09-09 | Indonesia
9 | Elset | Agass | eagass8@rambler.ru | Female | 2004-06-26 | China
10 | Aurore | Drillingcourt | adrillingcourt9@cnet.com | Female | 1977-10-19 | China
11 | Ilse | Goldman | igoldmana@ihg.com | Female | 2001-07-31 | Mongolia
12 | Penny | Nayer | pnayerb@harvard.edu | Female | 1969-02-05 | Colombia
13 | Neale | Dubery | nduberyc@soundcloud.com | Male | 1975-12-22 | Portugal
14 | Gnni | Dickman | gdickmand@people.com.cn | Female | 1977-10-12 | Guatemala
15 | Flori | Giroldi | fgiroldie@ameblo.jp | Female | 1975-11-14 | China
--More--
Alternatively, we can specify our required field names:
Command
SELECT id, first_name, last_name FROM person;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name
------+----------------+---------------------
1 | Ronda | Skermer
2 | Hamid | Abbett
3 | Francis | Nickerson
4 | Erminie | M'Quharg
5 | Teodoro | Trimmill
6 | Reilly | Amesbury
7 | West | Elphey
8 | Letta | Caurah
9 | Elset | Agass
10 | Aurore | Drillingcourt
--More--
1.2. ORDER BY
The ORDER BY keyword is used to sort the result-set in ascending (ASC) or descending (DESC) order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in ascending order, use the DESC keyword.
1.2.1. ASC
For ascending order:
Command
SELECT * FROM person ORDER BY country_of_birth;Output
1
2
3
4
5
6
7
8
9
10
11
12
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+----------------+---------------------+-----------------------------------------+--------+---------------+----------------------------------
475 | Koren | Burgen | | Female | 1985-09-16 | Afghanistan
223 | Collen | Raubheim | craubheim66@gravatar.com | Female | 1968-01-31 | Afghanistan
331 | Vaughan | Borles | vborles96@behance.net | Male | 1987-09-08 | Albania
831 | Cordy | Aries | | Male | 2007-07-06 | Albania
662 | Una | Chevis | uchevisid@whitehouse.gov | Female | 2001-10-03 | Albania
993 | Delmar | Sparham | | Male | 2000-01-24 | Albania
583 | Nikolia | Whodcoat | nwhodcoatg6@army.mil | Female | 1993-01-01 | Albania
751 | Kyrstin | Wimpenny | kwimpennyku@slideshare.net | Female | 1986-07-12 | Algeria
837 | Dalis | McLinden | | Male | 1989-09-24 | Angola
--More--
1.2.2. DESC
For dscending order:
Command
SELECT * FROM person ORDER BY country_of_birth DESC;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+----------------+---------------------+-----------------------------------------+--------+---------------+----------------------------------
563 | Meredeth | Pantin | | Male | 1971-02-22 | Zambia
173 | Pennie | Christauffour | pchristauffour4s@scientificamerican.com | Male | 2004-04-16 | Zambia
947 | Saidee | Daffern | sdaffernqa@barnesandnoble.com | Female | 1973-03-11 | Yemen
742 | Lacee | Sumner | lsumnerkl@icio.us | Female | 2007-03-31 | Yemen
520 | Clerissa | Mockett | | Female | 1980-12-08 | Yemen
89 | Robinson | Tichner | | Male | 2005-12-09 | Yemen
754 | Oren | Eidler | oeidlerkx@typepad.com | Male | 1969-02-23 | Yemen
725 | Sadye | Garman | | Female | 1985-11-05 | Yemen
537 | Isadore | Tasker | itaskerew@example.com | Male | 1977-03-05 | Vietnam
602 | Nevins | Blenkinship | nblenkinshipgp@psu.edu | Male | 2010-02-04 | Vietnam
--More--
Date of birth in dscending order:
Command
SELECT * FROM person ORDER BY date_of_birth DESC;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+----------------+---------------------+-----------------------------------------+--------+---------------+----------------------------------
307 | Penni | Privost | | Female | 2010-08-07 | Indonesia
43 | Kathye | Bottleson | kbottleson16@google.pl | Female | 2010-06-27 | China
616 | Darryl | Craw | dcrawh3@nba.com | Male | 2010-05-30 | Guatemala
549 | Paulie | Durante | pdurantef8@go.com | Female | 2010-05-09 | Russia
983 | Elka | Chyuerton | | Female | 2010-04-28 | China
533 | Leslie | Lusgdin | llusgdines@creativecommons.org | Female | 2010-04-20 | Bosnia and Herzegovina
248 | Shurwood | Vezey | svezey6v@amazon.com | Male | 2010-04-15 | Indonesia
974 | Noll | Pidgin | npidginr1@wiley.com | Male | 2010-04-13 | Indonesia
676 | Edwina | Presdee | epresdeeir@icio.us | Female | 2010-04-10 | China
813 | Terri | Blockey | tblockeymk@gnu.org | Female | 2010-04-08 | China
--More--
1.2.3. ORDER BY with Two-parameter
This means that if country_of_birth is the same, then the rows will be sorted according to the id column. Check the difference with the previous one and this.
Command
SELECT * FROM person ORDER BY country_of_birth, id;Output
1
2
3
4
5
6
7
8
9
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
------+----------------+---------------------+-----------------------------------------+--------+---------------+----------------------------------
223 | Collen | Raubheim | craubheim66@gravatar.com | Female | 1968-01-31 | Afghanistan
475 | Koren | Burgen | | Female | 1985-09-16 | Afghanistan
331 | Vaughan | Borles | vborles96@behance.net | Male | 1987-09-08 | Albania
583 | Nikolia | Whodcoat | nwhodcoatg6@army.mil | Female | 1993-01-01 | Albania
662 | Una | Chevis | uchevisid@whitehouse.gov | Female | 2001-10-03 | Albania
831 | Cordy | Aries | | Male | 2007-07-06 | Albania
--More--
1.3. DISTINCT
The SELECT DISTINCT statement is used to return only distinct (different) values.
Command
SELECT DISTINCT country_of_birth FROM person ORDER BY country_of_birth;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
country_of_birth
----------------------------------
Afghanistan
Albania
Algeria
Angola
Argentina
Armenia
Australia
Azerbaijan
Bangladesh
Belarus
Benin
Bolivia
Bosnia and Herzegovina
Brazil
--More--
1.4. WHERE
The WHERE clause is used to extract only those records that fulfill a specified condition.
Command
SELECT * FROM person WHERE gender='Female';Output
1
2
3
4
5
6
7
8
9
10
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+----------------+---------------------+---------------------------------------+--------+---------------+--------------------------
1 | Ronda | Skermer | rskermer0@arstechnica.com | Female | 1993-06-30 | Argentina
4 | Erminie | M'Quharg | emquharg3@e-recht24.de | Female | 1999-03-13 | Mozambique
8 | Letta | Caurah | lcaurah7@yale.edu | Female | 1994-09-09 | Indonesia
9 | Elset | Agass | eagass8@rambler.ru | Female | 2004-06-26 | China
10 | Aurore | Drillingcourt | adrillingcourt9@cnet.com | Female | 1977-10-19 | China
11 | Ilse | Goldman | igoldmana@ihg.com | Female | 2001-07-31 | Mongolia
12 | Penny | Nayer | pnayerb@harvard.edu | Female | 1969-02-05 | Colombia
--More--
1.4.1. BETWEEN
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
Command
SELECT * FROM person WHERE date_of_birth BETWEEN '1985-02-02' AND '1986-06-04';Output
1
2
3
4
5
6
7
8
9
10
11
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+------------+--------------+------------------------------+--------+---------------+-----------------------
25 | Billi | Dybbe | bdybbeo@samsung.com | Female | 1986-02-22 | Brazil
37 | Sorcha | Tunesi | stunesi10@adobe.com | Female | 1986-04-12 | Philippines
45 | Carleen | Dzeniskevich | cdzeniskevich18@disqus.com | Female | 1985-06-18 | China
103 | Oberon | Sparry | osparry2u@yellowbook.com | Male | 1985-09-22 | China
125 | Cal | Shurville | cshurville3g@1und1.de | Male | 1986-01-29 | Qatar
157 | Juline | Wanek | | Female | 1985-11-30 | Sweden
162 | Amelia | Braferton | | Female | 1986-05-03 | New Zealand
168 | West | Glowacz | wglowacz4n@yolasite.com | Male | 1985-12-02 | Canada
--More--
1.4.2. LIKE
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
-
%: The percent sign represents zero, one, or multiple characters -
_: The underscore represents a single character
Find all emails ending with disqus.com:
Command
SELECT * FROM person WHERE email LIKE '%disqus.com';Output
1
2
3
4
5
id | first_name | last_name | email | gender | date_of_birth | country_of_birth
-----+------------+--------------+----------------------------+--------+---------------+------------------
45 | Carleen | Dzeniskevich | cdzeniskevich18@disqus.com | Female | 1985-06-18 | China
852 | Alex | Garmans | agarmansnn@disqus.com | Male | 1990-11-08 | China
(2 rows)
1.5. GROUP BY
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of persons in each country".
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
Command
SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
14
country_of_birth | count
----------------------------------+-------
Bangladesh | 1
Indonesia | 109
Venezuela | 5
Cameroon | 3
Czech Republic | 18
Sweden | 31
Dominican Republic | 7
Ireland | 3
Macedonia | 4
Papua New Guinea | 2
Sri Lanka | 1
--More--
1.5.1. GROUP BY with ORDER BY
Command
SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth ORDER BY country_of_birth;Output
1
2
3
4
5
6
7
8
9
10
11
12
country_of_birth | count
----------------------------------+-------
Afghanistan | 2
Albania | 5
Algeria | 1
Angola | 2
Argentina | 20
Armenia | 5
Australia | 1
Azerbaijan | 3
Bangladesh | 1
--More--
1.5.2. GROUP BY HAVING
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
Command
SELECT country_of_birth, COUNT(*) FROM person GROUP BY country_of_birth HAVING COUNT(*) > 50 ORDER BY country_of_birth;Output
1
2
3
4
5
6
country_of_birth | count
------------------+-------
China | 180
Indonesia | 109
Russia | 56
(3 rows)
1.6. COALESCE
The COALESCE() function returns the first non-null value in a list.
Command
SELECT COALESCE(email, 'Email not provided') FROM person;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
coalesce
-----------------------------------------
rskermer0@arstechnica.com
habbett1@cbc.ca
fnickerson2@mac.com
emquharg3@e-recht24.de
Email not provided
ramesbury5@businessinsider.com
Email not provided
lcaurah7@yale.edu
eagass8@rambler.ru
adrillingcourt9@cnet.com
igoldmana@ihg.com
pnayerb@harvard.edu
--More--
1.7. Another Table Called car
Now we will download a new bunch of data to create another table called car. This table has these columns:
- id: Primary key
- make: Company name of the car
- model: Model of the car
- price: Price of the car, price between in a nice range
Now edit the downloded file car.sql a bit—
1
2
3
4
5
6
7
8
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
);
-- More lines containing INSERT command, We are not showing them here.
After saving our changed car.sql file, now we will execute it.
test=# \i /path/to/car.sqlHere is first 10 rows from car table. LIMIT is used to get only first 10 rows.
Command
SELECT * FROM car LIMIT 10;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
id | make | model | price
----+------------+------------------+-----------
1 | Daewoo | Leganza | 241058.40
2 | Mitsubishi | Montero | 269595.21
3 | Kia | Rio | 245275.16
4 | GMC | Savana 1500 | 217435.26
5 | Jaguar | X-Type | 41665.96
6 | Lincoln | Mark VIII | 163843.38
7 | GMC | Rally Wagon 3500 | 231169.05
8 | Cadillac | Escalade ESV | 279951.34
9 | Volvo | XC70 | 269436.96
10 | Isuzu | Rodeo | 65421.58
(10 rows)
1.8. Basic Functions
1.8.1. MAX
The MAX() function returns the largest value of the selected column.
Command
SELECT MAX(price) FROM car;Output
1
2
3
4
max
-----------
299959.83
(1 row)
Command
SELECT make, MAX(price) FROM car GROUP BY make LIMIT 5;Output
1
2
3
4
5
6
7
8
make | max
----------+-----------
Ford | 290993.39
Smart | 159887.95
Maserati | 221349.10
Dodge | 299766.43
Infiniti | 298245.19
(5 rows)
1.8.2. MIN
The MIN() function returns the smallest value of the selected column.
Command
SELECT MIN(price) FROM car;Output
1
2
3
4
min
----------
30348.16
(1 row)
Command
SELECT make, MIN(price) FROM car GROUP BY make LIMIT 5;Output
1
2
3
4
5
6
7
8
make | min
----------+-----------
Ford | 31021.48
Smart | 159887.95
Maserati | 38668.83
Dodge | 33495.17
Infiniti | 47912.88
(5 rows)
1.8.3. AVG
The AVG() function returns the average value of a numeric column.
Command
SELECT AVG(price) FROM car;Output
1
2
3
4
avg
---------------------
164735.601300000000
(1 row)
Command
SELECT make, AVG(price) FROM car GROUP BY make LIMIT 5;Output
1
2
3
4
5
6
7
8
make | avg
----------+---------------------
Ford | 171967.729473684211
Smart | 159887.950000000000
Maserati | 122897.857500000000
Dodge | 166337.502307692308
Infiniti | 179690.643846153846
(5 rows)
1.8.4. ROUND
The PostgreSQL ROUND() function rounds a numeric value to its nearest integer or a number with the number of decimal places.
Command
SELECT ROUND(AVG(price)) FROM car;Output
1
2
3
4
round
--------
164736
(1 row)
Command
SELECT make, ROUND(AVG(price)) FROM car GROUP BY make LIMIT 5;Output
1
2
3
4
5
6
7
8
9
make | round
----------+--------
Ford | 171968
Smart | 159888
Maserati | 122898
Dodge | 166338
Infiniti | 179691
(5 rows)
1.8.5. COUNT
The COUNT() function returns the number of rows that match a specified criterion.
Command
SELECT COUNT(make) FROM car;Output
1
2
3
4
count
-------
1000
(1 row)
1.8.6. SUM
The SUM() function returns the total sum of a numeric column.
Command
SELECT SUM(price) FROM car;Output
1
2
3
4
sum
--------------
164735601.30
(1 row)
Command
SELECT make, SUM(price) FROM car GROUP BY make LIMIT 5;Output
1
2
3
4
5
6
7
8
make | sum
----------+-------------
Ford | 16336934.30
Smart | 159887.95
Maserati | 491591.43
Dodge | 8649550.12
Infiniti | 2335978.37
(5 rows)
1.9. Basic Arithmetic Operations
Command
SELECT 10 + 2;Output
1
2
3
4
?column?
----------
12
(1 row)
Command
SELECT 10 / 2;Output
1
2
3
4
?column?
----------
5
(1 row)
Command
SELECT 10^2;Output
1
2
3
4
?column?
----------
100
(1 row)
1.10. Discount Calculation
Now suppose the company offers a 10% discount on all cars. We will now calculate the amount of this 10%, and calculate the new price.
Command
SELECT id, make, model, price, ROUND(price * 0.10, 2), ROUND(price - (price * 0.10), 2) FROM car;Output
1
2
3
4
5
6
7
8
9
10
11
12
13
id | make | model | price | round | round
------+---------------+----------------------+-----------+----------+-----------
1 | Daewoo | Leganza | 241058.40 | 24105.84 | 216952.56
2 | Mitsubishi | Montero | 269595.21 | 26959.52 | 242635.69
3 | Kia | Rio | 245275.16 | 24527.52 | 220747.64
4 | GMC | Savana 1500 | 217435.26 | 21743.53 | 195691.73
5 | Jaguar | X-Type | 41665.96 | 4166.60 | 37499.36
6 | Lincoln | Mark VIII | 163843.38 | 16384.34 | 147459.04
7 | GMC | Rally Wagon 3500 | 231169.05 | 23116.91 | 208052.15
8 | Cadillac | Escalade ESV | 279951.34 | 27995.13 | 251956.21
9 | Volvo | XC70 | 269436.96 | 26943.70 | 242493.26
10 | Isuzu | Rodeo | 65421.58 | 6542.16 | 58879.42
--More--
ROUND (source [ , n ] ) function rounds a numeric value to its nearest integer or a number with the number of decimal places. Where The source argument is a number or a numeric expression that is to be rounded and the n argument is an integer that determines the number of decimal places after rounding.
1.11. ALIAS
SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.
Command
1
2
3
4
SELECT id, make, model, price AS original_price,
ROUND(price * 0.10, 2) AS ten_percent_discount,
ROUND(price - (price * 0.10), 2) AS discounted_price
FROM car;
Output
1
2
3
4
5
6
7
8
9
10
11
12
13
id | make | model | original_price | ten_percent_discount | discounted_price
------+---------------+----------------------+----------------+----------------------+------------------
1 | Daewoo | Leganza | 241058.40 | 24105.84 | 216952.56
2 | Mitsubishi | Montero | 269595.21 | 26959.52 | 242635.69
3 | Kia | Rio | 245275.16 | 24527.52 | 220747.64
4 | GMC | Savana 1500 | 217435.26 | 21743.53 | 195691.73
5 | Jaguar | X-Type | 41665.96 | 4166.60 | 37499.36
6 | Lincoln | Mark VIII | 163843.38 | 16384.34 | 147459.04
7 | GMC | Rally Wagon 3500 | 231169.05 | 23116.91 | 208052.15
8 | Cadillac | Escalade ESV | 279951.34 | 27995.13 | 251956.21
9 | Volvo | XC70 | 269436.96 | 26943.70 | 242493.26
10 | Isuzu | Rodeo | 65421.58 | 6542.16 | 58879.42
--More--
1.12. NULLIF
The NULLIF() function returns NULL if two expressions are equal. Otherwise, it returns the first expression.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
test=# SELECT NULLIF(2, 1);
nullif
--------
2
(1 row)
test=# SELECT NULLIF('a', 'b');
nullif
--------
a
(1 row)
test=# SELECT NULLIF(0, 0);
nullif
--------
(1 row)
1.13. DATE
PostgreSQL provides several functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:
1
2
3
4
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
1
2
3
4
5
6
7
8
SELECT CURRENT_TIME;
Result: 14:39:53.662522-05
SELECT CURRENT_DATE;
Result: 2001-12-23
SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05
PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:
1
2
3
4
5
transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()
1.13.1. NOW
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
test=# SELECT NOW();
now
------------------------------
2020-08-19 23:39:49.18778+06
(1 row)
test=# SELECT NOW()::DATE;
now
------------
2020-08-19
(1 row)
test=# SELECT NOW()::TIME;
now
-----------------
23:40:44.645625
(1 row)
1.13.2. Addition and Subtraction of Date
INTERVAL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
test=# SELECT NOW() - INTERVAL '1 YEAR';
?column?
-------------------------------
2019-08-19 23:47:11.475305+06
(1 row)
test=# SELECT NOW() - INTERVAL '10 YEAR';
?column?
-------------------------------
2010-08-19 23:47:31.627347+06
(1 row)
test=# SELECT NOW() - INTERVAL '3 MONTHS';
?column?
-------------------------------
2020-05-19 23:47:53.403383+06
(1 row)
test=# SELECT NOW() + INTERVAL '40 DAYS';
?column?
-------------------------------
2020-09-28 23:48:31.419856+06
(1 row)
1.13.3. EXTRACT
The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can, therefore, be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision.
EXTRACT(field FROM source) 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
test=# SELECT NOW();
now
------------------------------
2020-08-19 23:55:42.13778+06
(1 row)
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(CENTURY FROM NOW());
date_part
-----------
21
(1 row)
1.13.4. AGE
age(timestamp, timestamp)or
age(timestamp)The return type of both is an interval.
Command
SELECT first_name, last_name, gender, date_of_birth, AGE(NOW(), date_of_birth) AS age FROM person;Output
1
2
3
4
5
6
7
8
9
10
11
12
first_name | last_name | gender | date_of_birth | age
----------------+---------------------+--------+---------------+------------------------------------------
Ronda | Skermer | Female | 1993-06-30 | 27 years 1 mon 19 days 23:56:04.414053
Hamid | Abbett | Male | 1995-08-31 | 24 years 11 mons 19 days 23:56:04.414053
Francis | Nickerson | Male | 1998-03-16 | 22 years 5 mons 3 days 23:56:04.414053
Erminie | M'Quharg | Female | 1999-03-13 | 21 years 5 mons 6 days 23:56:04.414053
Teodoro | Trimmill | Male | 1982-04-30 | 38 years 3 mons 19 days 23:56:04.414053
Reilly | Amesbury | Male | 1990-12-31 | 29 years 7 mons 19 days 23:56:04.414053
West | Elphey | Male | 2004-03-29 | 16 years 4 mons 21 days 23:56:04.414053
Letta | Caurah | Female | 1994-09-09 | 25 years 11 mons 10 days 23:56:04.414053
Elset | Agass | Female | 2004-06-26 | 16 years 1 mon 23 days 23:56:04.414053
--More--
See More: Date/Time Types