DBA TO CLOUD AND BIGDATA SOLUTION: 08/14/19

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?

----------

(1 row)

firstdb=#

REMAINDER #

firstdb=# SELECT 10 % 3;

?column?

----------

(1 row)

firstdb=# SELECT 10 % 4;

?column?

----------

(1 row)

firstdb=# SELECT 10 % 6;

?column?

----------

(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;

------+---------------+----------------------+----------+---------+-------

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;

------+---------------+----------------------+----------+---------+---------------------------

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 row)

firstdb=# SELECT COALESCE(null,1) AS number;

number

--------

(1 row)

firstdb=# SELECT COALESCE(null,null,1) AS number;

number

--------

(1 row)

firstdb=# SELECT COALESCE(null,null,1,10) AS number;

number

--------

(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

SELECT 10 / 0;

NULLIF FUCTION #

firstdb=# SELECT NULLIF(10,1);

nullif

--------

(1 row)

firstdb=# SELECT NULLIF(10,19);

nullif

--------

(1 row)

firstdb=# SELECT NULLIF(10,20);

nullif

--------

(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?

----------

(1 row)

firstdb=# SELECT 20 / NULLIF ( 10,5) ;

?column?

----------

(1 row)

firstdb=#

firstdb=# SELECT COALESCE ( 10 / NULLIF ( 0,0), 0);

coalesce

----------

(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=# 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

-----------

(1 row)

EXTRACT DAYS #

firstdb=# SELECT EXTRACT(DAY FROM NOW());

date_part

-----------

(1 row)

firstdb=#

EXTRACT CENTURY #

firstdb=# SELECT EXTRACT(CENTURY FROM NOW());

date_part

-----------

(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;

----+------------+-----------+--------------------------+--------+---------------+------------------

(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%';

----+------------+-----------+--------------------------+--------+---------------+------------------

(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=#

selct * from person4 where email=’ zcarryer77@nydailynews.com’

-----+------------+-----------+----------------------------+--------+---------------+------------------

(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=#

UPDATE COMMANDS#

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=#

------------+------------+------------------+--------+---------------+------------------

(8 rows)

HOW TO IGNORE CONSTRAINTS DURING INSERT STATEMENTS#

select * from person WHERE id=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;

------------+-----------+----------------------+--------+---------------+------------------+------

(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)

----+------------+-----------+------------------+--------+---------------+------------------+--------

(1 row)

DBA TO CLOUD AND BIGDATA SOLUTION

Wednesday, August 14, 2019

PostgreSQL 11 Commands

Step by Step YugabyteDB 2.11 (Open Source) Distributed DB - Multi-node Cluster Setup on RHEL

BLOCKROLL

Report Abuse