PostgreSQL doesn't provide internal function to generate UUID's, use uuid-ossp
CREATE EXTENSION IFNOTEXISTS"uuid-ossp";SELECT uuid_generate_v1(); uuid_generate_v1 -------------------------------------- 4d459e0c-fb3e-11eb-a638-0242ac110002-- pure randomnessSELECT uuid_generate_v4(); uuid_generate_v4 -------------------------------------- 418f39e5-8a46-4da2-8cea-884904f45d6fCREATETABLEproducts_uuid ( id uuid default uuid_generate_v1(), product_name varchar(100) not null);INSERT INTO products_uuid (product_name) VALUES ('ice cream'),('cake'),('candies');SELECT*FROM products_uuid; id | product_name --------------------------------------+-------------- 5cf1dbe0-fb3e-11eb-a638-0242ac110002 | ice cream 5cf1df28-fb3e-11eb-a638-0242ac110002 | cake 5cf1df46-fb3e-11eb-a638-0242ac110002 | candiesCREATETABLEproducts_uuid_v4 ( id uuid default uuid_generate_v4(), product_name varchar(100) not null);INSERT INTO products_uuid_v4 (product_name) VALUES ('ice cream'),('cake'),('candies');SELECT*FROM products_uuid_v4;learning=# SELECT*FROM products_uuid_v4; id | product_name --------------------------------------+-------------- 83b74bed-2cf8-4e26-80b0-c7c7b2e5f3e7 | ice cream ac563251-7a95-408d-966b-ed5ecc1f228d | cake 1079f6d3-b0c3-40ef-bd2e-da4467b63432 | candies
HSTORE
stores data in key-value pairs
key and VALUES are text string only
CREATE EXTENSION IFNOTEXISTS hstore;CREATETABLEtable_hstore ( id SERIALPRIMARY KEY , title varchar(100) not null, book_info hstore);INSERT INTO table_hstore (title, book_info) VALUES('Title 1', ' "publisher" => "ABC publisher" , "paper_cost" => "100" , "e_cost" => "5.85" ');SELECT*FROM table_hstore; id | title | book_info 1 | Title 1 | "e_cost"=>"5.85", "publisher"=>"ABC publisher", "paper_cost"=>"100"SELECT book_info ->'publisher'as publisher FROM table_hstore; publisher --------------- ABC publisher
Json
PostgreSQL supports both
JSON
BSON or JSONB ( Binary JSON )
JSONB has full support for indexing
CREATETABLEtable_json ( id SERIALPRIMARY KEY , docs json);INSERT INTO table_json (docs) VALUES ('[1,2,3,4,5,6]'),('{"key":"value"}');INSERT INTO table_json (docs)VALUES ('[{"key":"value"},{"key2":"value2"}]');SELECT*FROM table_json; id | docs ----+-------------------------------------1 | [1,2,3,4,5,6]2 | {"key":"value"}3 | [{"key":"value"},{"key2":"value2"}]ALTERTABLE table_json alter column docs type jsonb;SELECT*FROM table_json where docs @>'2'; id | docs ----+--------------------1 | [1, 2, 3, 4, 5, 6]CREATEindexon table_json USING GIN (docs jsonb_path_ops);
Network Address Data Types
Name
Storage Size
Notes
cidr
7 or 19 bytes
IPv4 and IPv6 networks
inet
7 or 19 bytes
IPv4 and IPv6 hosts and networks
macaddr
6 bytes
MAC addresses
macaddr8
8 bytes
MAC addresses ( EUI 64-bit )
It is better to use these types instead of plain text types of store network address, because these types offer input error checking and specialised operators and functions
Supports indexing and advance operations
CREATETABLEtable_netaddr ( id SERIALPRIMARY KEY , ip inet);INSERT INTO table_netaddr (ip)VALUES ('148.77.50.74'), ('110.158.172.66'), ('176.103.251.175'), ('84.84.14.58'), ('141.122.225.161'), ('78.44.113.33'), ('81.236.254.9'), ('82.116.85.21'), ('54.64.79.223'), ('162.240.78.253');SELECT*FROM table_netaddr LIMIT5; id | ip ----+-----------------1 | 148.77.50.742 | 110.158.172.663 | 176.103.251.1754 | 84.84.14.585 | 141.122.225.161SELECT ip, set_masklen(ip,24) as inet_24, set_masklen(ip::cidr,24) as cidr_24 , set_masklen(ip::cidr,27) as cidr_27, set_masklen(ip::cidr,28) as cidr_28 FROM table_netaddr LIMIT2; ip | inet_24 | cidr_24 | cidr_27 | cidr_28 148.77.50.74 | 148.77.50.74/24 | 148.77.50.0/24 | 148.77.50.64/27 | 148.77.50.64/28110.158.172.66 | 110.158.172.66/24 | 110.158.172.0/24 | 110.158.172.64/27 | 110.158.172.64/28