Uday Yadav

Posted onSep 2, 2021 • Edited onSep 7, 2021

SQL : DataTypes

#sql #database #programming

This guide is for PostgreSQL :https://www.postgresql.org/

Getting Started with PostgreSQL :
https://dev117uday.gitbook.io/databases/sql/getting-started

Loading Sample Data Guide :
https://dev117uday.gitbook.io/databases/sql/getting-started/load-data

Boolean Data

TRUE
FALSE
NULL

TRUE	FALSE
TRUE	FALSE
'true'	'false'
't'	'f'
'yes'	'no'
'y'	'n'
'1'	'0'

CREATETABLEbooltable(idSERIALPRIMARYKEY,is_enableBOOLEANNOTNULL);INSERTINTObooltable(is_enable)VALUES(TRUE),('true'),('y'),('yes'),('t'),('1');INSERTINTObooltable(is_enable)VALUES(FALSE),('false'),('n'),('no'),('f'),('0');SELECT*FROMbooltable;SELECT*FROMbooltableWHEREis_enable='y';SELECT*FROMbooltableWHERENOTis_enable;

Character Data

Character Type	Notes
CHARACTER (N), CHAR (N)	fixed-length, blank padded
CHARACTER VARYING (N), VARCHAR(N)	variable length with length limit
TEXT, VARCHAR	variable unlimited length, max 1GB

n is default to 1

-- INPUTSELECTCAST('Uday'ascharacter(10))as"name";-- OUTPUT"Uday      "-- INPUTSELECT'Uday'::character(10)as"name";-- OUTPUT"Uday      "-- INPUTSELECT'uday'::varchar(10);-- OUTPUT"uday"-- INPUTSELECT'lorem ipsum'::text;-- OUTPUT"lorem ipsum"

Numeric Data

Types	Notes
Integers	whole number, +ve and -ve
Fixed-point, floating point	for fractions of whole nu

type	size (bytes)	min	max
smallint	2	-32678	32767
integer	4	-2,147,483,648	2,147,483,647
bigint	8	-9223372036854775808	9223372036854775807

type	size	range
smallserial	2	1 to 32767
serial	4	1 to 2147483647
bigserial	8	1 to 9223372036854775807

Fixed Point Data

numeric ( precision , scale ) | decimal ( precision , scale )

precision : max number of digits to the left and right of the decimal point
scale : number of digits allowable on the right of the decimal point

Floating Point Data

Type	Notes
Real	allows precision to six decimal digits
Double precision	allows precision to 15 digits points of precision

type	size	storage type	Range
numeric, decimal	variable	fixed point	131072 digits before decimal point and 16383 digits after the decimal point
real	4	floating point	6 decimal digits precision
double precision	8	floating point	15 decimal digits precision

CREATETABLEtable_numbers(col_numericnumeric(20,5),col_realreal,col_doubledoubleprecision);INSERTINTOtable_numbers(col_numeric,col_real,col_double)VALUES(.9,.9,.9),(3.34675,3.34675,3.34675),(4.2345678910,4.2345678910,4.2345678910);SELECT*FROMtable_numbers;-- OUTPUTlearning=#select*fromtable_numbers;col_numeric|col_real|col_double-------------+----------+-------------0.90000|0.9|0.93.34675|3.34675|3.346754.23457|4.234568|4.234567891(3rows)

Hierarchical order to SELECT best type : numeric > decimal > float

Date Time Data

type	stores	low	high
Date	date only	4713 BC	294276 AD
Time	time only	4713 BC	5874897 AD
Timestamp	date and time	4713 BC	294276 AD
`Timestampz`	date, time and timezone	4713 BC	294276 AD
Interval	difference btw time

Date type

CREATETABLEtable_dates(idserialprimarykey,employee_namevarchar(100)notnull,hire_dateDATENOTNULL,add_dateDATEDEFAULTCURRENT_DATE);INSERTINTOtable_dates(employee_name,hire_date)VALUES('uday','2020-02-02'),('another uday','2020-02-01');SELECT*FROMtable_dates;SELECTNOW();

Time type

CREATETABLEtable_time(idserialprimarykey,class_namevarchar(10)notnull,start_timetimenotnull,end_timetimenotnull);INSERTINTOtable_time(class_name,start_time,end_time)VALUES('maths','08:00:00','08:55:00'),('chemistry','08:55:00','09:00:00');SELECT*FROMtable_time;-- OUTPUTid|class_name|start_time|end_time----+------------+------------+----------1|maths|08:00:00|08:55:002|chemistry|08:55:00|09:00:00(2rows)SELECTCURRENT_TIME;current_time--------------------07:21:00.163354+00(1row)SELECTCURRENT_TIME(2);current_time----------------07:21:14.96+00(1row)SELECTLOCALTIME;localtime-----------------07:21:36.717509(1row)SELECTtime'12:10'-time'04:30'asRESULT;result----------07:40:00(1row)-- format : interval 'n type'-- n = number-- type : second, minute, hours, day, month, year ....SELECTCURRENT_TIME,CURRENT_TIME+INTERVAL'2 hours'asRESULT;current_time|result--------------------+--------------------07:22:06.241919+00|09:22:06.241919+00(1row)SELECTCURRENT_TIME,CURRENT_TIME+INTERVAL'-2 hours'asRESULT;current_time|result--------------------+--------------------07:22:16.644727+00|05:22:16.644727+00(1row)

Timestamp and Timezone

timestamp : stores time without time zone
timestamptz : timestamp with time zone , stored using UTC format
adding timestamp to timestamptz without mentioning the zone will result in server automatically assumes timezone to system's timezone
Internally, PostgreSQL will store the timezoneaccurately but then OUTPUTting the data, will it be converted according to your timezone

SELECTnameFROMpg_timezone_nameswherename='posix/Asia/Calcutta';SETTIMEZONE='Asia/Calcutta';SELECTNOW()::TIMESTAMP;now----------------------------2021-08-1212:53:03.971433(1row)CREATETABLEtable_time_tz(tstimestamp,tstztimestamptz);INSERTINTOtable_time_tz(ts,tstz)VALUES('2020-12-22 10:10:10','2020-12-22 10:10:10.009+05:30');SELECT*FROMtable_time_tz;ts|tstz---------------------+-------------------------------2020-12-2210:10:10|2020-12-2210:10:10.009+05:30(1row)SELECTCURRENT_TIMESTAMP;current_timestamp---------------------------------2021-08-1212:53:29.54762+05:30(1row)SELECTtimezone('Asia/Singapore','2020-01-01 00:00:00')timezone---------------------2020-01-0102:30:00(1row)

UUID

UUID : Universal Unique Identifier
PostgreSQL doesn't provide internal function to generate UUID's, useuuid-ossp

CREATEEXTENSIONIFNOTEXISTS"uuid-ossp";SELECTuuid_generate_v1();uuid_generate_v1--------------------------------------4d459e0c-fb3e-11eb-a638-0242ac110002-- pure randomnessSELECTuuid_generate_v4();uuid_generate_v4--------------------------------------418f39e5-8a46-4da2-8cea-884904f45d6fCREATETABLEproducts_uuid(iduuiddefaultuuid_generate_v1(),product_namevarchar(100)notnull);INSERTINTOproducts_uuid(product_name)VALUES('ice cream'),('cake'),('candies');SELECT*FROMproducts_uuid;id|product_name--------------------------------------+--------------5cf1dbe0-fb3e-11eb-a638-0242ac110002|icecream5cf1df28-fb3e-11eb-a638-0242ac110002|cake5cf1df46-fb3e-11eb-a638-0242ac110002|candiesCREATETABLEproducts_uuid_v4(iduuiddefaultuuid_generate_v4(),product_namevarchar(100)notnull);INSERTINTOproducts_uuid_v4(product_name)VALUES('ice cream'),('cake'),('candies');SELECT*FROMproducts_uuid_v4;learning=#SELECT*FROMproducts_uuid_v4;id|product_name--------------------------------------+--------------83b74bed-2cf8-4e26-80b0-c7c7b2e5f3e7|icecreamac563251-7a95-408d-966b-ed5ecc1f228d|cake1079f6d3-b0c3-40ef-bd2e-da4467b63432|candies

HSTORE

stores data in key-value pairs
key and VALUES are text string only

CREATEEXTENSIONIFNOTEXISTShstore;CREATETABLEtable_hstore(idSERIALPRIMARYKEY,titlevarchar(100)notnull,book_infohstore);INSERTINTOtable_hstore(title,book_info)VALUES('Title 1',' "publisher" => "ABC publisher" ,     "paper_cost" => "100" , "e_cost" => "5.85" ');SELECT*FROMtable_hstore;id|title|book_info1|Title1|"e_cost"=>"5.85","publisher"=>"ABC publisher","paper_cost"=>"100"SELECTbook_info->'publisher'aspublisherFROMtable_hstore;publisher---------------ABCpublisher

Json

PostgreSQL supports both
- JSON
- BSON or JSONB ( Binary JSON )
JSONB has full support for indexing

CREATETABLEtable_json(idSERIALPRIMARYKEY,docsjson);INSERTINTOtable_json(docs)VALUES('[1,2,3,4,5,6]'),('{"key":"value"}');INSERTINTOtable_json(docs)VALUES('[{"key":"value"},{"key2":"value2"}]');SELECT*FROMtable_json;id|docs----+-------------------------------------1|[1,2,3,4,5,6]2|{"key":"value"}3|[{"key":"value"},{"key2":"value2"}]ALTERTABLEtable_jsonaltercolumndocstypejsonb;SELECT*FROMtable_jsonwheredocs@>'2';id|docs----+--------------------1|[1,2,3,4,5,6]CREATEindexontable_jsonUSINGGIN(docsjsonb_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(idSERIALPRIMARYKEY,ipinet);INSERTINTOtable_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*FROMtable_netaddrLIMIT5;id|ip----+-----------------1|148.77.50.742|110.158.172.663|176.103.251.1754|84.84.14.585|141.122.225.161SELECTip,set_masklen(ip,24)asinet_24,set_masklen(ip::cidr,24)ascidr_24,set_masklen(ip::cidr,27)ascidr_27,set_masklen(ip::cidr,28)ascidr_28FROMtable_netaddrLIMIT2;ip|inet_24|cidr_24|cidr_27|cidr_28148.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

Top comments(0)

For further actions, you may consider blocking this person and/orreporting abuse

Movatterモバイル変換

DEV Community