firstdb=# SELECT * from PERSON
JOIN
CAR ON person.car_id=car.id;
firstdb=#
-[ RECORD 1 ]----+-----------------
id | 1
first_name | Fernanda
last_name | Beardon
email | hello1@gmail.com
gender | Female
date_of_birth | 1952-10-02
country_of_birth | india
car_id | 10
id | 10
make | honda
model | onda
price | 12600.00
-[ RECORD 2 ]----+-----------------
id | 2
first_name | Ashok
last_name | Beardon
email | hello2@gmail.com
gender | Female
date_of_birth | 1952-10-02
:
LEFT JOIN
SELECT * from person;
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
2 | Ashok | Beardon | hello2@gmail.com | Female |
1952-10-02 | india | 11
3 | Ashok | Beardon | hello2@gmail.com | Female |
1952-10-02 | india | 12
4 | Vimal | John | yub@gmail.com | Male
| 1960-10-02 | india |
(4 rows)
(END)
select * from car;
id | make |
model | price
----+------------+----------+----------
10 | honda | onda
| 12600.00
11 | Land Rover | Sterling |
87665.38
12 | GMC | Acadia | 17662.69
(3 rows)
firstdb=# ^C
firstdb=#
++++++++++++++++++++
How to find out the records who don;t have foreign key constraints by using Left join, the steps are below :-
SELECT * from person WHERE car_id is NULL;
firstdb=# id | first_name |
last_name | email | gender | date_of_birth |
country_of_birth | car_id
firstdb-# ----+------------+-----------+---------------+--------+---------------+------------------+--------
firstdb-# 4 | Vimal | John | yub@gmail.com | Male | 1960-10-02 | india |
firstdb-# (1 row)
firstdb-#
firstdb-#
Vimal don't have car id , he is not owning car. But when you use Left join, it will shows all records including who don;t have foreign key constraints.
The below JOIN statement will retrieve records with constraints only.
SELECT * FROM person
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
----+------------+-----------+------------------+--------+---------------+------------------+--------+----+------------+----------+----------
1 | Fernanda | Beardon
| hello1@gmail.com | Female | 1952-10-02 | india | 10 | 10 | honda | onda
| 12600.00
2 | Ashok | Beardon | hello2@gmail.com | Female |
1952-10-02 | india | 11 | 11 | Land Rover | Sterling | 87665.38
3 | Ashok | Beardon | hello2@gmail.com | Female |
1952-10-02 | india | 12 | 12 | GMC | Acadia | 17662.69
(3 rows)
LEFT JOIN gives all records :-
SELECT * FROM person
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
----+------------+-----------+------------------+--------+---------------+------------------+--------+----+------------+----------+----------
1 | Fernanda | Beardon
| hello1@gmail.com | Female | 1952-10-02 | india | 10 | 10 | honda | onda
| 12600.00
2 | Ashok | Beardon | hello2@gmail.com | Female |
1952-10-02 | india | 11 | 11 | Land Rover | Sterling | 87665.38
3 | Ashok | Beardon | hello2@gmail.com | Female |
1952-10-02 | india | 12 | 12 | GMC | Acadia | 17662.69
4 | Vimal | John | yub@gmail.com | Male
| 1960-10-02 | india | |
| | |
(4 rows)
(END)
DELETING RECORDS WHEN YOUR
HAVING THE FORIGN KEY
1. you can delete the records from person who don’t
have foreign key constraints
2. you can make this car id is null then you can
delete the records.
firstdb=#
DELETE FROM car where id=12;
ERROR: update or delete on table "car"
violates foreign key constraint "person_car_id_fkey" on table
"person"
DETAIL: Key (id)=(12) is still referenced from table
"person".
firstdb=#
step 1:-
firstdb=#
DELETE FROM person WHERE id=3;
DELETE
1
step 2:-
firstdb=#
DELETE FROM car where id=12;
DELETE
1
firstdb=#
now you have deleted the records from CAR table after deleting child record from person table.
Exporting Query results in
CSV FORMAT: -
SELECT
* FROM person;
ashokkumar@worldpgdb:~/Documents$
mkdir -p export_rec
ashokkumar@worldpgdb:~/Documents$
chmod -R 7777 export_rec
firstdb=#
\copy (SELECT * FROM person LEFT JOIN car on car_id=person.car_id) TO
'/home/ashokkumar/Documents/export_rec/results.csv' DELIMITER ',' csv HEADER;
COPY
4
firstdb=#
 |
| results.csv opened in LibreOffice in ubuntu |
SERIAL & SEQUENCE NUMBERS
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(50) |
| not null |
date_of_birth | date | | not null |
country_of_birth | character
varying(150) | | not null |
car_id | bigint | | |
Indexes:
"person_pkey" PRIMARY
KEY, btree (id)
"person_car_id_key"
UNIQUE CONSTRAINT, btree (car_id)
Foreign-key constraints:
"person_car_id_fkey"
FOREIGN KEY (car_id) REFERENCES car(id)
(END)
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
2 | Ashok | Beardon | hello2@gmail.com | Female |
1952-10-02 | india | 11
(2 rows)
firstdb=# SELECT nextval('person_id_seq'::regclass);
nextval
---------
9
(1 row)
firstdb=# SELECT * FROM person_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
9 | 24 | t
(1 row)
firstdb=#
firstdb=# select * from car;
id | make
| model |
price
----+------------+----------+----------
10 | honda | onda
| 12600.00
11 | Land Rover | Sterling |
87665.38
(2 rows)
firstdb=# insert into person (first_name, last_name, email, gender,
date_of_birth, country_of_birth,car_id) values ('Vicky', 'Priya',
'vpriya@biglobe.ne.jp', 'Female', '03/02/1989', 'Liberia',11);
ERROR: duplicate key value
violates unique constraint "person_car_id_key"
DETAIL: Key (car_id)=(11) already
exists.
firstdb=# insert into car (id,make,model,price)
firstdb-# values
firstdb-# (16,'Honda','i 10',60000);
INSERT 0 1
firstdb=# insert into person (first_name, last_name, email, gender,
date_of_birth, country_of_birth,car_id) values ('Vicky', 'Priya',
'vpriya@biglobe.ne.jp', 'Female', '03/02/1989', 'Liberia',16);
INSERT 0 1
firstdb=#
ALTER sequence person_id_seq RESTART WITH 10;
firstdb=# ALTER sequence
person_id_seq RESTART WITH 10;
ALTER SEQUENCE
firstdb=#
firstdb=# SELECT * FROM person_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
10 | 0 | f
(1 row)
firstdb=#
firstdb=# insert into person (first_name, last_name, email, gender,
date_of_birth, country_of_birth) values ('Revathy', 'Ashok', 'reva@gmail.com',
'Female', '10/10/1989', 'Oman');
INSERT 0 1
firstdb=#
select * from person where first_name='Revathy';
firstdb=#
sequence id taken as 10.
id | first_name | last_name | email
| gender | date_of_birth | country_of_birth | car_id
----+------------+-----------+----------------+--------+---------------+------------------+--------
10 | Revathy | Ashok
| reva@gmail.com | Female | 1989-10-10
| Oman |
(1 row)
firstdb=# insert into person (first_name, last_name, email, gender,
date_of_birth, country_of_birth) values ('Poornima', 'Ashok',
'poorna@biglobe.ne.jp', 'Female', '03/02/1989', 'Liberia');
INSERT 0 1
firstdb=# id | first_name | last_name | email | gender | date_of_birth |
country_of_birth | car_id
----+------------+-------id | first_name | last_name | email | gender | date_of_birth |
country_of_birth | car_id
----+------------+-----------+----------------------+--------+---------------+------------------+--------
11 | Poornima | Ashok
| poorna@biglobe.ne.jp | Female | 1989-03-02 | Liberia |
(1
row)----+----------------------+--------+---------------+------------------+--------
11 | Poornima | Ashok
| poorna@biglobe.ne.jp | Female | 1989-03-02 | Liberia |
(1 row)
id | first_name | last_name |
email | gender |
date_of_birth | country_of_birth | car_id
----+------------+-----------+----------------------+--------+---------------+------------------+--------
11 | Poornima | Ashok
| poorna@biglobe.ne.jp | Female | 1989-03-02 | Liberia |
(1 row)
EXTENSION#
Postgresql is designed to be easily extensible. For this reason,extension
loaded into the database can function just like features that are built in.
SELECT * FROM
pg_available_extenstions;
name | default_version | installed_version
|
comment
--------------------+-----------------+-------------------+----------------------------------------------------------------------
pgrowlocks | 1.2 | | show row-level locking
information
moddatetime | 1.0 | | functions for tracking last
modification time
earthdistance | 1.1 | | calculate great-circle
distances on the surface of the Earth
intagg | 1.1 | | integer aggregator and
enumerator (obsolete)
btree_gin | 1.3 | | support for indexing
common datatypes in GIN
ltree | 1.1 | | data type for hierarchical
tree-like structures
tcn | 1.0 | | Triggered change notifications
dict_int | 1.0 | | text search dictionary
template for integers
dict_xsyn | 1.0 | | text search dictionary
template for extended synonym processing
btree_gist | 1.5 | | support for indexing
common datatypes in GiST
pageinspect | 1.7 | | inspect the contents
UNDERSTANDING UUID datatype:
Universally Unique Identifiers (UUID)
|
