Tuesday, August 20, 2019

PostgreSQL Join Query, Query Results in CSV format

INNER JOIN


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)









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

Scope - ·        Infrastructure planning and requirement for installation of the multi-node cluster database ·        Prerequisites Software...