Part-19 Data Types :- UUID | JSON | HSTORE | ARRAY | User-defined

 UUID: value is 128-bit quantity generated by an algorithm that make it unique

it relies on the third-party modules It is specific algorithms to generate UUIDs. 

For example Let check  uuid-ossp module is installed or not .  you use the CREATE EXTENSION statement as follows:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Code language: SQL (Structured Query Language) (sql)

The IF NOT EXISTS clause allows you to avoid re-installing the module.

There is two funcation :

1:- uuid_generate_v1() :UUID values based on the combination of computer’s MAC address, current timestamp, and a random value,

2:- uuid_generate_v4() :UUID value solely based on random numbers

SELECT uuid_generate_v1();

Output :"67720950-16d7-11ec-967d-a0d3c1723253"

SELECT uuid_generate_v4();

 Output :""01baeea1-1839-4bec-8ac9-3e69710ae1aa""

 
 CREATE TABLE PHONE_BOOK (
    contact_id uuid DEFAULT uuid_generate_v4 (),
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR,
    PRIMARY KEY (contact_id)
);

INSERT INTO contacts (first_name, last_name, email, phone)   VALUES
    (   'John',
        'Smith',
        'john.smith@example.com',
        '408-237-2345'
    );

JSON:

JSON stands for JavaScript Object Notation. JSON is an open standard format that consists of key-value pairs.

The main usage of JSON is to transport data between a server and a web application. Unlike other formats, JSON is human-readable text.

CREATE TABLE PIZZA (
    id serial NOT NULL PRIMARY KEY,
    info json NOT NULL
); 

INSERT INTO PIZZA (info)
VALUES('{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}');
 

SELECT * FROM  PIZZA1;

1"{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}"
2;"{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}"
3;"{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}"
4;"{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}"

;
SELECT info ->> 'customer' as customer FROM  PIZZA1

 "John Doe"
"Lily Bush"
"Josh William"
"Mary Clark"

 
SELECT *  FROM PIZZA  where info ->> 'customer' = 'John Doe'

1;"{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}"

SELECT info ->> 'customer'  FROM PIZZA  where info ->> 'customer' = 'John Doe'

"John Doe"

SELECT info -> 'items' ->> 'product' as items FROM  PIZZA 

where info -> 'items' ->> 'product' = 'Beer'

"Beer"

SELECT info -> 'items' ->> 'product' as items  ,info -> 'items' ->> 'qty' as product
FROM  PIZZA ORDER BY product;

"Toy Car";"1"
"Toy Train";"2"
"Diaper";"24"
"Beer";"6"


SELECT info -> 'customer' AS customer
FROM PIZZA;

""John Doe""
""Lily Bush""
""Josh William""
""Mary Clark""


SELECT info ->> 'customer' AS customer  FROM PIZZA;

"John Doe"
"Lily Bush"
"Josh William"
"Mary Clark"

SELECT info -> 'items' ->> 'product' as product
FROM PIZZA ORDER BY product;

"Beer"
"Diaper"
"Toy Car"
"Toy Train"
 

SELECT info ->> 'customer' AS customer,  info -> 'items' ->> 'product' AS product  FROM  PIZZA   

WHERE CAST ( info -> 'items' ->> 'qty' AS INTEGER) = 2

"Mary Clark";"Toy Train"

SELECT
   MIN (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))
FROM orders;

Output: 1;24;33;8.2500000000000000