PostgreSQL
Postgresql 11 version :-
LOAD THE DATA USING SQL FILE COMMAND#
firstdb=# \i
/home/ashokkumar/Documents/CAR.sql;
SELECT 1=1;
firstdb=# SELECT first_name,email from person where email like '_p%';
firstdb=# SELECT first_name,email from person where email like 'p%';
firstdb=# SELECT first_name,email from person where email like 'a%';
DISTINCT VALUE CHECK#
firstdb=# SELECT count(DISTINCT
country_of_birth) from person;
count
-------
138
(1 row)
GROUP BY #
firstdb=# SELECT country_of_birth FROM
person GROUP BY country_of_birth;
firstdb=#
country_of_birth | count
----------------------------------+-------
Afghanistan | 10
Albania | 8
Angola | 1
Argentina | 25
Armenia | 5
Australia | 1
Azerbaijan | 5
Bangladesh | 4
Belarus | 2
Belgium | 2
Belize | 1
Bermuda | 1
Bosnia and Herzegovina |
3
Botswana | 1
Brazil | 46
Bulgaria | 6
Burkina Faso | 1
Cambodia | 1
:
HAVING BY#
firstdb=# SELECT
country_of_birth ,count(*) FROM person GROUP BY country_of_birth HAVING
COUNT(*) >=180 ORDER BY country_of_birth;
country_of_birth | count
------------------+-------
China |
242
(1 row)
firstdb=#
country_of_birth | count
----------------------------------+-------
Afghanistan | 10
Albania | 8
Angola | 1
Argentina | 25
Armenia | 5
Australia | 1
Azerbaijan | 5
Bangladesh | 4
Belarus | 2
Belgium | 2
Belize | 1
Bermuda | 1
Bosnia and Herzegovina |
3
Botswana | 1
Brazil | 46
Bulgaria | 6
Burkina Faso | 1
Cameroon | 1
Canada | 17
Chad | 1
Chile | 4
Colombia | 19
Costa Rica | 1
Croatia | 6
Cuba | 6
Cyprus | 3
Czech Republic | 24
SUM() FUNCTION #
firstdb=#
SELECT make,SUM(price) FROM car_1 GROUP BY make;
firstdb=#
make
| sum
---------------+------------
GMC
| 2105248.79
Maybach
| 219391.58
Lincoln |
784375.00
Honda
| 1461747.14
Ram
| 325693.80
Spyker
| 72990.35
Daewoo
| 44955.00
Ford
| 4766652.03
Scion
| 339289.28
Maserati
| 452065.23
Dodge
| 3190185.02
Chevrolet
| 4914536.20
Infiniti
| 777985.67
Saturn
| 88613.43
MINI
| 116449.80
Bentley
| 454185.80
Austin |
128886.76
Pontiac | 2542565.30
Porsche
| 658037.20
Plymouth |
844576.34
Holden
| 21789.31
Audi
| 1362097.47
Rolls-Royce
| 178153.89
Jaguar
| 313428.66
Lexus
| 1385060.30
:
MATHEMATIC CALCULATIONS:-
EXPONENT #
^
firstdb=#
SELECT 10^2;
?column?
----------
100
(1 row)
NOT EQUAL TO #
firstdb=#
SELECT 5!;
?column?
----------
120
(1 row)
firstdb=#
MULTIPLICATION AND DIVISION #
firstdb=#
SELECT 10 * 2 / 8;
?column?
----------
2
(1 row)
firstdb=#
REMAINDER #
firstdb=#
SELECT 10 % 3;
?column?
----------
1
(1 row)
firstdb=#
SELECT 10 % 4;
?column?
----------
2
(1 row)
firstdb=#
SELECT 10 % 6;
?column?
----------
4
(1 row)
firstdb=#
ARITHMATIC CALCULATION IN TABLE COLUMNS #
firstdb=#
SELECT id,make,model,price, price / 100 * 10 FROM car_1;
id |
make | model |
price | ?column?
------+---------------+----------------------+----------+-----------------------
1 | Dodge | Dakota | 56288.90 |
5628.8900000000000000
2 | Holden | VS Commodore | 21789.31 | 2178.9310000000000000
3 | Hyundai | Sonata | 64932.39 |
6493.2390000000000000
4 | Volvo | V70 | 45066.09 |
4506.6090000000000000
5 | Mazda | Mazda3 | 19566.21 |
1956.6210000000000000
6 | Eagle | Talon | 72707.34 |
7270.7340000000000000
7 | Land Rover | Defender Ice Edition | 67059.55 |
6705.9550000000000000
8 | Maserati | Quattroporte | 18138.36 | 1813.8360000000000000
9 | Porsche | Boxster | 26172.74 |
2617.2740000000000000
10 | Ford | Econoline E150 | 17637.03 | 1763.7030000000000000
11 | Saturn | Outlook | 20212.05 |
2021.2050000000000000
12 | Honda | Accord | 97726.35 |
9772.6350000000000000
13 | Kia | Rondo | 84345.09 |
8434.5090000000000000
14 | Land Rover | Defender Ice Edition | 32545.50 |
3254.5500000000000000
firstdb=# SELECT id,make,model,price, ROUND(price *
.10,2), ROUND( price - ( price * .10)) AS discount_after_10_percent FROM CAR_1;
id |
make | model |
price | round
| round
------+---------------+----------------------+----------+---------+-------
1 | Dodge | Dakota | 56288.90 | 5628.89 | 50660
2 | Holden | VS Commodore | 21789.31 | 2178.93 | 19610
3 | Hyundai | Sonata | 64932.39 | 6493.24 | 58439
4 | Volvo | V70 | 45066.09 | 4506.61 | 40559
5 | Mazda | Mazda3 | 19566.21 | 1956.62 | 17610
6 | Eagle | Talon | 72707.34 | 7270.73 | 65437
7 | Land Rover | Defender Ice Edition | 67059.55 | 6705.96
| 60354
8 | Maserati | Quattroporte | 18138.36 | 1813.84 | 16325
9 | Porsche | Boxster | 26172.74 | 2617.27 | 23555
10 | Ford | Econoline E150 | 17637.03 | 1763.70 | 15873
11 | Saturn | Outlook | 20212.05 | 2021.21 | 18191
SELECT id,make,model,price AS original_price,
ROUND(price * .10,2) AS ten_percent, ROUND(price - ( price * .10) AS
discount_after_10_percent FROM car_1;
id |
make | model |
price | round
| discount_after_10_percent
------+---------------+----------------------+----------+---------+---------------------------
1 | Dodge | Dakota | 56288.90 | 5628.89 | 50660
2 | Holden | VS Commodore | 21789.31 | 2178.93 | 19610
3 | Hyundai | Sonata | 64932.39 | 6493.24 | 58439
4 | Volvo | V70 | 45066.09 | 4506.61 | 40559
5 | Mazda | Mazda3 | 19566.21 | 1956.62 | 17610
6 | Eagle | Talon | 72707.34 | 7270.73 | 65437
7 | Land Rover | Defender Ice Edition | 67059.55 | 6705.96
| 60354
8 | Maserati | Quattroporte | 18138.36 | 1813.84 | 16325
9 | Porsche | Boxster | 26172.74 | 2617.27 | 23555
10 | Ford | Econoline E150 | 17637.03 | 1763.70 | 15873
11 | Saturn | Outlook | 20212.05 | 2021.21 | 18191
12 | Honda | Accord | 97726.35 | 9772.64 | :
COALESCE#####
firstdb=#
SELECT COALESCE(1) AS number;
number
--------
1
(1 row)
firstdb=#
SELECT COALESCE(null,1) AS number;
number
--------
1
(1 row)
firstdb=#
SELECT COALESCE(null,null,1) AS number;
number
--------
1
(1 row)
firstdb=#
SELECT COALESCE(null,null,1,10) AS number;
number
--------
1
(1 row)
firstdb=#
FILL THE EMPTY SPACE WITH STATEMENTS USING COALESCE
FUNCTION#
firstdb=#
SELECT COALESCE(email) FROM person;
firstdb=#
SELECT COALESCE(email, 'Email not
provided') FROM person;
firstdb=#
coalesce
----------------------------------------
alowles0@biglobe.ne.jp
Email
not provided
mdunlea7@house.gov
lteese8@hao123.com
meytelc@hibu.com
ssteinhamf@storify.com
sfobidgej@baidu.com
scolviek@jalbum.net
kkarbyl@icq.com
Email
not provided
ehunnywellv@freewebs.com
dvonhindenburgx@telegraph.co.uk
sebley@shareasale.com
fanderl12@elpais.com
Email
not provided
rzebedee15@wisc.edu
Email
not provided
jgulliver1d@pen.io
Email
not provided
sdole1l@ucoz.com
jcawthorn1m@wix.com
Email
not provided
anestor1t@geocities.com
Email not provided
Email not provided
SELECT 10 / 0;
NULLIF FUCTION
#
firstdb=#
SELECT NULLIF(10,1);
nullif
--------
10
(1 row)
firstdb=#
SELECT NULLIF(10,19);
nullif
--------
10
(1 row)
firstdb=#
SELECT NULLIF(10,20);
nullif
--------
10
(1 row)
firstdb=#
SELECT NULLIF(100,19);
nullif
--------
100
(1 row)
firstdb=#
SELECT NULLIF(100,100);
nullif
--------
(1 row)
firstdb=#
DIVISION AND NULLIF FUNCTIONS #
^
firstdb=#
SELECT 10 / NULLIF (2,9);
?column?
----------
5
(1 row)
firstdb=#
SELECT 20 / NULLIF ( 10,5) ;
?column?
----------
2
(1 row)
firstdb=#
firstdb=# SELECT
COALESCE ( 10 / NULLIF ( 0,0), 0);
coalesce
----------
0
(1 row)
firstdb=#
DATE
CHECK AND MATH IN POSTGRESQL
CURRENT
DATE WITH TIMESTAMP #
firstdb=#
SELECT NOW();
now
----------------------------------
2019-08-13 18:41:02.265175+05:30
(1 row)
firstdb=#
CURRENT DATE#
firstdb=#
SELECT NOW()::DATE;
now
------------
2019-08-13
(1 row)
firstdb=#
CURRENT TIMESTAMP#
SELECT NOW()::TIMESTAMP;
firstdb=#
SELECT NOW()::TIMESTAMP;
now
----------------------------
2019-08-13 18:48:27.623865
(1 row)
TIMESTAMP
WITHOUT TIMEZONE CHECK#
SELECT NOW()::TIMESTAMP WITHOUT TIMEZONE;
firstdb=#
SELECT NOW()::TIMESTAMP WITHOUT TIME ZONE;
now
----------------------------
2019-08-13 18:49:08.873747
(1 row)
firstdb=#
SUBTRACT 10 DAYS FROM CURRENT DATE #
firstdb=#
SELECT NOW() - INTERVAL '10 DAYS';
?column?
---------------------------------
2019-08-03 18:52:51.85522+05:30
(1 row)
firstdb=#
ADD 10
DAYS WITH CURRENT DATE #
firstdb=#
SELECT NOW() + INTERVAL ' 10 DAYS ';
?column?
----------------------------------
2019-08-23 18:54:05.457728+05:30
(1 row)
firstdb=#
ADD 10 DAYS WITH CURRENT DATE THEN EXTRACT ONLY
DATE #
firstdb=#
SELECT (NOW() + INTERVAL ' 10 DAYS')::DATE;
date
------------
2019-08-23
(1 row)
firstdb=#
SELECT NOW();
now
----------------------------------
2019-08-13 19:01:35.092965+05:30
(1 row)
firstdb=#
firstdb=#
firstdb=#
SELECT (NOW() + INTERVAL ' 356 DAYS')::DATE;
date
------------
2020-08-03
(1 row)
firstdb=#
ADD MONTHS IN CURRENT DATE #
ADD 10
MONTHS #
firstdb=#
SELECT NOW() + INTERVAL ' 10 MONTHS';
?column?
---------------------------------
2020-06-13 18:56:26.21424+05:30
(1 row)
firstdb=#
ADD MONTHS THEN CHECK DATE #
firstdb=#
SELECT NOW()::DATE + INTERVAL ' 10 MONTHS';
?column?
---------------------
2020-06-13 00:00:00
(1 row)
firstdb=#
firstdb=#
SELECT (NOW() + INTERVAL ' 10 MONTHS'):: DATE;
date
------------
2020-06-13
(1 row)
firstdb=#
EXTRACT
FUNCTION IN DATE #
EXTRACT
YEAR #
SELECT
EXTRACT(YEAR FROM NOW());
firstdb=#
SELECT NOW();
now
----------------------------------
2019-08-13 19:02:22.483177+05:30
(1 row)
firstdb=# SELECT
EXTRACT(YEAR FROM NOW());
date_part
-----------
2019
(1 row)
firstdb=#
EXTRACT
MONTH #
firstdb=#
SELECT EXTRACT(MONTH FROM NOW());
date_part
-----------
8
(1 row)
EXTRACT
DAYS #
firstdb=#
SELECT EXTRACT(DAY FROM NOW());
date_part
-----------
13
(1 row)
firstdb=#
EXTRACT
CENTURY #
firstdb=#
SELECT EXTRACT(CENTURY FROM NOW());
date_part
-----------
21
(1 row)
firstdb=#
AGE #
firstdb=# \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(50) | | not null |
date_of_birth | date | | not null |
country_of_birth | character varying(150)
| | |
firstdb=#
AGE() FUNCTION #
firstdb=# SELECT
first_name,last_name,gender,date_of_birth, AGE(NOW(),date_of_birth) FROM
person;
firstdb=#
first_name
| last_name | gender | date_of_birth | age
-----------------+----------------+--------+---------------+--------------------------------------
Aurora | Lowles | Female | 2019-03-02 | 5 mons 11 days 19:20:20.229327
Tobi
| Spellacey | Female |
2018-04-09 | 1 year 4 mons 4 days
19:20:20.229327
Mattie
| Dunlea | Male | 2019-04-07 | 4 mons 6 days 19:20:20.229327
Loraine
| Teese | Female |
2019-10-01 | -1 mons -18 days
-04:39:39.770673
Major
| Eytel | Male | 2018-08-11 | 1 year 2 days 19:20:20.229327
Sky
| Steinham | Male | 2018-11-12 | 9
mons 1 day 19:20:20.229327
Shawnee | Fobidge | Female | 2019-03-06 | 5 mons 7 days 19:20:20.229327
Sharron
| Colvie | Female |
2019-02-04 | 6 mons 9 days
19:20:20.229327
Karon
| Karby | Female |
2018-05-11 | 1 year 3 mons 2 days
19:20:20.229327
Sarita
| Mackiewicz | Female |
2019-02-05 | 6 mons 8 days
19:20:20.229327
Erminie
| Hunnywell | Female |
2019-08-01 | 12 days 19:20:20.229327
Dani
| Von Hindenburg | Male |
2019-09-04 | -21 days -04:39:39.7
EXTRACT
YEAR AND FIND THE CURRENT DATE FROM DATE_OF_BIRTH COLUMN IN PERSON TABLE #
firstdb=#
SELECT first_name,last_name,gender,date_of_birth, EXTRACT(YEAR FROM
AGE(NOW(),date_of_birth)) FROM person;
firstdb=#
first_name
| last_name | gender | date_of_birth | date_part
-----------------+----------------+--------+---------------+-----------
Aurora | Lowles | Female | 2019-03-02 |
0
Tobi
| Spellacey | Female |
2018-04-09 | 1
Mattie
| Dunlea | Male | 2019-04-07 |
0
Loraine
| Teese | Female |
2019-10-01 | 0
Major
| Eytel | Male | 2018-08-11 |
1
Sky
| Steinham | Male | 2018-11-12 |
0
Shawnee | Fobidge | Female | 2019-03-06 |
0
Sharron
| Colvie | Female |
2019-02-04 | 0
Karon
| Karby | Female |
2018-05-11 | 1
Sarita
| Mackiewicz | Female |
2019-02-05 | 0
Erminie
| Hunnywell | Female |
2019-08-01 | 0
Dani
| Von Hindenburg | Male |
2019-09-04 | 0
Sherwood
| Eble | Male | 2019-09-03 |
0
Florenza
| Anderl | Female |
2019-10-07 | 0
Freemon
| Colston | Male | 2018-08-11 |
1
Rutherford | Zebedee | Male
| 2018-10-12 | 0
Carlin
| Hughill | Female |
2018-08-10 | 1
Johnathon
| Gulliver | Male | 2019-10-04 |
0
firstdb=# SELECT
first_name,last_name,gender,date_of_birth,EXTRACT(MONTHS FROM
AGE(NOW(),date_of_birth)) FROM person;
firstdb=#
first_name
| last_name | gender | date_of_birth | date_part
-----------------+----------------+--------+---------------+-----------
Aurora | Lowles | Female | 2019-03-02 |
5
Tobi | Spellacey | Female | 2018-04-09 |
4
Mattie
| Dunlea | Male | 2019-04-07 |
4
Loraine
| Teese | Female |
2019-10-01 | -1
Major
| Eytel | Male | 2018-08-11 |
0
Sky
| Steinham | Male | 2018-11-12 |
9
Shawnee | Fobidge | Female | 2019-03-06 |
5
Sharron
| Colvie | Female |
2019-02-04 | 6
Karon
| Karby | Female |
2018-05-11 | 3
Sarita
| Mackiewicz | Female |
2019-02-05 | 6
Erminie
| Hunnywell | Female |
2019-08-01 | 0
Dani
| Von Hindenburg | Male |
2019-09-04 | 0
Sherwood
| Eble | Male | 2019-09-03 |
0
PRIMARY
KEYS
UNDERSTANDING
PRIMARY KEYS
SELECT * FROM
person LIMIT 1;
id
| first_name | last_name |
email | gender |
date_of_birth | country_of_birth
----+------------+-----------+--------------------------+--------+---------------+------------------
1 | Cletus
| Lethbrig |
clethbrig0@reference.com | Male |
2019-06-24 | Japan
(1
row)
firstdb=#
insert into person4 (id, first_name, last_name, email, gender, date_of_birth,
country_of_birth) values (1, 'Cletus', 'Lethbrig', 'clethbrig0@reference.com',
'Male', '2019/06/24', 'Japan');
ERROR: duplicate key value violates unique
constraint "person4_pkey"
DETAIL: Key (id)=(1) already exists.
firstdb=#
drop primary
key → "person4_pkey"
List of relations
Schema |
Name | Type
| Owner
--------+----------------+----------+----------
public | car_1 | table | postgres
public | car_1_id_seq | sequence | postgres
public | person4 | table | postgres
public | person4_id_seq | sequence | postgres
(4 rows)
firstdb=#
firstdb=# ALTER
TABLE person4 DROP CONSTRAINT person4_pkey;
ALTER TABLE
firstdb=#
firstdb=#
insert into person4 (id, first_name, last_name, email, gender, date_of_birth,
country_of_birth) values (1, 'Cletus', 'Lethbrig', 'clethbrig0@reference.com',
'Male', '2019/06/24', 'Japan');
INSERT 0 1
firstdb=#
SELECT * FROM
person4 where first_name like '%Cletus%';
id | first_name | last_name | email | gender | date_of_birth |
country_of_birth
----+------------+-----------+--------------------------+--------+---------------+------------------
1 | Cletus
| Lethbrig |
clethbrig0@reference.com | Male |
2019-06-24 | Japan
1 | Cletus
| Lethbrig |
clethbrig0@reference.com | Male |
2019-06-24 | Japan
(2 rows)
firstdb=#
ALTER TABLE person4 ADD PRIMARY KEY (id);
ERROR: could not create unique index
"person4_pkey"
DETAIL: Key (id)=(1) is duplicated.
firstdb=#
we have to
delete the duplicate records
firstdb=#
DELETE FROM person4 WHERE id = 1;
DELETE 2
firstdb=#
firstdb=#
insert into person4 (id, first_name, last_name, email, gender, date_of_birth,
country_of_birth) values (1, 'Cletus', 'Lethbrig', 'clethbrig0@reference.com',
'Male', '2019/06/24', 'Japan');
INSERT 0 1
firstdb=#
ADD PRIMARY KEY
#
firstdb=# ALTER
TABLE person4 ADD PRIMARY KEY (id);
ALTER TABLE
firstdb=#
Table
"public.person4"
Column
| Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+-------------------------------------
id | bigint | | not null |
nextval('person4_id_seq'::regclass)
first_name | character varying(50) |
| not null |
last_name | character varying(50) |
| not null |
email | character varying(150) | | not null |
gender | character varying(7) |
| not null |
date_of_birth | date | | not null |
country_of_birth | character varying(50) |
| |
Indexes:
"person4_pkey" PRIMARY KEY, btree
(id)
UNIQUE
CONTRAINTS
SELECT email,
count(*) from person GROUP BY email ;
email | count
----------------------------------------+-------
dspringthorpcm@typepad.com | 1
fpriddingrr@reverbnation.com |
1
kmcardleey@biblegateway.com | 1
gsecreten@stanford.edu | 1
klawly4o@cbsnews.com | 1
ltevelovl7@networksolutions.com |
1
statem59@omniture.com | 1
vmowattc6@joomla.org | 1
swissby27@skyrock.com | 1
cvandersong2@nbcnews.com | 1
zcarryer77@nydailynews.com | 1
we have
duplication records#
firstdb=#
SELECT email, count(*) from person4 GROUP BY email HAVING COUNT(*) > 1;
email | count
----------------------------+-------
zcarryer77@nydailynews.com | 2
(1 row)
firstdb=#
id |
first_name | last_name |
email | gender |
date_of_birth | country_of_birth
-----+------------+-----------+----------------------------+--------+---------------+------------------
259 | Haskel | Imlaw
| zcarryer77@nydailynews.com | Male
| 2019-04-03 | Vietnam
260 | Zachery | Carryer
| zcarryer77@nydailynews.com | Male
| 2018-11-07 | Portugal
(2 rows)
ADD UNIQUE CONSTRAINTS WITH CUSTOM NAME #
ALTER TABLE person4 ADD CONSTRAINT unique_email_address UNIQUE(email)
firstdb=# ALTER
TABLE person4 ADD CONSTRAINT unique_email_address UNIQUE(email);
ERROR: could not create unique index
"unique_email_address"
DETAIL: Key (email)=(zcarryer77@nydailynews.com) is
duplicated.
firstdb=#
firstdb=#
DELETE from person4 where id=260;
DELETE 1
firstdb=#
firstdb=# ALTER
TABLE person4 add CONSTRAINT unique_email_address UNIQUE(email);
ALTER TABLE
firstdb=#
Table
"public.person4"
Column |
Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+-------------------------------------
id | bigint | | not null | nextval('person4_id_seq'::regclass)
first_name | character varying(50) |
| not null |
last_name | character varying(50) |
| not null |
email | character varying(150) | | not null |
gender
| character varying(7) |
| not null |
date_of_birth | date | | not null |
country_of_birth | character varying(50) |
| |
Indexes:
"person4_pkey" PRIMARY KEY, btree
(id)
"unique_email_address" UNIQUE
CONSTRAINT, btree (email)
(END)
firstdb=#
insert into person4 (id, first_name, last_name, email, gender, date_of_birth,
country_of_birth) values (260, 'Zachery', 'Carryer',
'zcarryer77@nydailynews.com', 'Male', '2018/11/07', 'Portugal');
ERROR: duplicate key value violates unique
constraint "unique_email_address"
DETAIL: Key (email)=(zcarryer77@nydailynews.com)
already exists.
firstdb=#
DROP UNIQUE CONSTRAINT #
firstdb=#
ALTER TABLE person4 DROP CONSTRAINT
unique_email_address;
ALTER TABLE
firstdb=# \d
person4;
firstdb=#
Table
"public.person4"
Column |
Type | Collation |
Nullable | Default
------------------+------------------------+-----------+----------+-------------------------------------
id | bigint | | not null |
nextval('person4_id_seq'::regclass)
first_name | character varying(50) |
| not null |
last_name
| character varying(50) |
| not null |
email | character varying(150) | | not null |
gender | character varying(7) |
| not null |
date_of_birth | date | | not null |
country_of_birth | character varying(50) |
| |
Indexes:
"person4_pkey" PRIMARY KEY, btree
(id)
ADD UNIQUE CONSTRAINTS DEFAULT #
firstdb=# ALTER
TABLE person4 ADD UNIQUE (email);
ALTER TABLE
firstdb=#
Table
"public.person4"
Column |
Type | Collation |
Nullable | Default
------------------+------------------------+-----------+----------+-------------------------------------
id | bigint | | not null |
nextval('person4_id_seq'::regclass)
first_name | character varying(50) |
| not null |
last_name | character varying(50) |
| not null |
email
| character varying(150) | | not null |
gender | character varying(7) |
| not null |
date_of_birth | date | | not null |
country_of_birth | character varying(50) |
| |
Indexes:
"person4_pkey" PRIMARY KEY, btree
(id)
"person4_email_key" UNIQUE
CONSTRAINT, btree (email)
ADD CHECK CONSTRAINTS#
firstdb=#
ALTER TABLE person ADD CONSTRAINT check_constraint1 CHECK(gender = 'Female' OR
gender = 'Male');
ALTER
TABLE
firstdb=#
firstdb=#
\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(50) |
| not null |
date_of_birth | date | | not null |
country_of_birth | character varying(150)
| | |
Check
constraints:
"check_constraint1" CHECK
(gender::text = 'Female'::text OR gender::text = 'Male'::text)
firstdb=#
firstdb=# UPDATE person SET email =
'ashokr@gmail.com' WHERE country_of_birth='Greece';
UPDATE 8
firstdb=# select * FROM person where country_of_birth='Greece';
firstdb=#
first_name | last_name | email
| gender | date_of_birth | country_of_birth
------------+------------+------------------+--------+---------------+------------------
Mirabella | Ruos
| ashokr@gmail.com | Female | 2019-06-29 | Greece
Billie | Trodler | ashokr@gmail.com | Female |
2019-06-16 | Greece
Cobby | Buckell | ashokr@gmail.com | Male | 2019-07-26 | Greece
Maxi | Cavy | ashokr@gmail.com | Female |
2018-09-16 | Greece
Honor | Biles | ashokr@gmail.com | Female |
2019-02-27 | Greece
Vicki | Ovill | ashokr@gmail.com | Female |
2019-06-13 | Greece
Parker | Corradetti | ashokr@gmail.com |
Male | 2019-06-05 | Greece
Irwinn | Lowell | ashokr@gmail.com | Male | 2019-03-19 | Greece
(8 rows)
HOW TO IGNORE CONSTRAINTS
DURING INSERT STATEMENTS#
select * from person WHERE id=1014;
first_name | last_name |
email | gender |
date_of_birth | country_of_birth |
id
------------+-----------+---------------------+--------+---------------+------------------+------
Sharron | Colvie
| scolviek@jalbum.net | Female | 2019-02-04 | Poland | 1014
(1 row)
firstdb=# insert into person (first_name, last_name, email, gender,
date_of_birth, country_of_birth,id) values
('Sharron', 'Colvie', 'scolviek@jalbum.net', 'Female', '02/04/2019', 'Poland',1014);
ERROR: duplicate key value
violates unique constraint "person_pkey"
DETAIL: Key (id)=(1014) already
exists.
firstdb=#
NOW TRY FOR UPDATE#
insert into person (first_name, last_name, email, gender, date_of_birth,
country_of_birth,id) values ('Ashok', 'Mani', 'AshokMani@jalbum.net', 'Male',
'02/04/2019', 'Poland',1014) ON CONFLICT (id) DO UPDATE SET
email= EXCLUDED.email,
last_name=EXCLUDED.last_name,first_name=EXCLUDED.first_name;
INSERT 0 1
select * from person WHERE id=1014;
first_name | last_name |
email | gender |
date_of_birth | country_of_birth |
id
------------+-----------+----------------------+--------+---------------+------------------+------
Ashok | Mani | AshokMani@jalbum.net | Female |
2019-02-04 | Poland | 1014
(1 row)
FOREIGH KEY #
firstdb=# create table person (
firstdb(# id BIGSERIAL NOT NULL
PRIMARY KEY,
firstdb(# first_name VARCHAR(50)
NOT NULL ,
firstdb(# last_name VARCHAR(50)
NOT NULL,
firstdb(# email VARCHAR(150),
firstdb(# gender VARCHAR(50) NOT
NULL,
firstdb(# date_of_birth DATE NOT
NULL,
firstdb(# country_of_birth
VARCHAR(150) NOT NULL,
firstdb(# car_id BIGINT
REFERENCES car(id),
firstdb(# UNIQUE(car_id)
firstdb(# );
CREATE TABLE
firstdb=#
firstdb=# create table car (
firstdb(# id BIGSERIAL NOT NULL
PRIMARY KEY,
firstdb(# make VARCHAR(100) NOT
NULL,
firstdb(# model VARCHAR(100) NOT
NULL,
firstdb(# price NUMERIC(19,2) NOT
NULL
firstdb(# );
CREATE TABLE
firstdb=# insert into person
(id,first_name,last_name,gender,email,date_of_birth,country_of_birth,car_id)
values
(1,'Fernanda','Beardon','Female','hello1@gmail.com','1952-10-02','india','10');
ERROR: insert or update on table
"person" violates foreign key constraint
"person_car_id_fkey"
DETAIL: Key (car_id)=(10) is not
present in table "car".
^
firstdb=# insert into car (id,make,model,price) values (
10,'honda','onda',12600);
INSERT 0 1
firstdb=# insert into person
(id,first_name,last_name,gender,email,date_of_birth,country_of_birth,car_id)
values
(1,'Fernanda','Beardon','Female','hello1@gmail.com','1952-10-02','india','10');
INSERT 0 1
firstdb=#
firstdb=# select * from car;
id | make | model |
price
----+-------+-------+----------
10 | honda | onda | 12600.00
(1 row)
id | first_name | last_name |
email | gender |
date_of_birth | country_of_birth | car_id
----+------------+-----------+------------------+--------+---------------+------------------+--------
1 | Fernanda | Beardon
| hello1@gmail.com | Female | 1952-10-02 | india | 10
(1 row)
