Data Types
just like programming

Data Types

Boolean Data

  • TRUE
  • FALSE
  • NULL
TRUE
FALSE
TRUE
FALSE
'true'
'false'
't'
'f'
'yes'
'no'
'y'
'n'
'1'
'0'
1
CREATE TABLE booltable (
2
id SERIAL PRIMARY KEY ,
3
is_enable BOOLEAN NOT NULL
4
);
5
​
6
INSERT INTO booltable (is_enable) VALUES (TRUE), ('true'),
7
('y') , ('yes'), ('t'), ('1');
8
INSERT INTO booltable (is_enable) VALUES (FALSE), ('false'),
9
('n') , ('no'), ('f'), ('0');
10
​
11
SELECT * FROM booltable;
12
​
13
SELECT * FROM booltable WHERE is_enable = 'y';
14
​
15
SELECT * FROM booltable WHERE NOT is_enable;
Copied!

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
1
-- INPUT
2
SELECT CAST('Uday' as character(10)) as "name";
3
-- OUTPUT
4
"Uday "
5
​
6
-- INPUT
7
SELECT 'Uday'::character(10) as "name";
8
-- OUTPUT
9
"Uday "
10
​
11
-- INPUT
12
SELECT 'uday'::varchar(10);
13
-- OUTPUT
14
"uday"
15
​
16
-- INPUT
17
SELECT 'lorem ipsum'::text;
18
-- OUTPUT
19
"lorem ipsum"
Copied!

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
1
CREATE TABLE table_numbers (
2
col_numeric numeric(20,5),
3
col_real real,
4
col_double double precision
5
);
6
​
7
INSERT INTO table_numbers (col_numeric,col_real,col_double)
8
VALUES (.9,.9,.9),
9
(3.34675,3.34675,3.34675),
10
(4.2345678910,4.2345678910,4.2345678910);
11
​
12
SELECT * FROM table_numbers;
13
​
14
-- OUTPUT
15
learning=# select * from table_numbers ;
16
col_numeric | col_real | col_double
17
-------------+----------+-------------
18
0.90000 | 0.9 | 0.9
19
3.34675 | 3.34675 | 3.34675
20
4.23457 | 4.234568 | 4.234567891
21
(3 rows)
Copied!
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

1
CREATE TABLE table_dates (
2
id serial primary key,
3
employee_name varchar(100) not null,
4
hire_date DATE NOT NULL,
5
add_date DATE DEFAULT CURRENT_DATE
6
);
7
​
8
INSERT INTO table_dates (employee_name, hire_date)
9
VALUES ('uday','2020-02-02'),('another uday','2020-02-01');
10
​
11
SELECT *
12
FROM table_dates;
13
​
14
SELECT NOW();
Copied!

Time type

1
CREATE TABLE table_time (
2
id serial primary key ,
3
class_name varchar(10) not null ,
4
start_time time not null ,
5
end_time time not null
6
);
7
​
8
INSERT INTO table_time (class_name, start_time, end_time)
9
VALUES ('maths','08:00:00','08:55:00'),
10
('chemistry','08:55:00','09:00:00');
11
​
12
SELECT * FROM table_time;
13
​
14
-- OUTPUT
15
​
16
id | class_name | start_time | end_time
17
----+------------+------------+----------
18
1 | maths | 08:00:00 | 08:55:00
19
2 | chemistry | 08:55:00 | 09:00:00
20
(2 rows)
21
​
22
​
23
SELECT CURRENT_TIME;
24
​
25
current_time
26
--------------------
27
07:21:00.163354+00
28
(1 row)
29
​
30
​
31
SELECT CURRENT_TIME(2);
32
​
33
current_time
34
----------------
35
07:21:14.96+00
36
(1 row)
37
​
38
​
39
SELECT LOCALTIME;
40
​
41
localtime
42
-----------------
43
07:21:36.717509
44
(1 row)
45
​
46
​
47
SELECT time '12:10' - time '04:30' as RESULT;
48
result
49
----------
50
07:40:00
51
(1 row)
52
​
53
​
54
-- format : interval 'n type'
55
-- n = number
56
-- type : second, minute, hours, day, month, year ....
57
​
58
SELECT CURRENT_TIME ,
59
CURRENT_TIME + INTERVAL '2 hours' as RESULT;
60
​
61
current_time | result
62
--------------------+--------------------
63
07:22:06.241919+00 | 09:22:06.241919+00
64
(1 row)
65
​
66
​
67
SELECT CURRENT_TIME ,
68
CURRENT_TIME + INTERVAL '-2 hours' as RESULT;
69
​
70
current_time | result
71
--------------------+--------------------
72
07:22:16.644727+00 | 05:22:16.644727+00
73
(1 row)
Copied!

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
1
SELECT name FROM pg_timezone_names
2
where name = 'posix/Asia/Calcutta';
3
​
4
SET TIMEZONE='Asia/Calcutta';
5
​
6
SELECT NOW()::TIMESTAMP;
7
​
8
now
9
----------------------------
10
2021-08-12 12:53:03.971433
11
(1 row)
12
​
13
​
14
CREATE TABLE table_time_tz (
15
ts timestamp,
16
tstz timestamptz
17
);
18
​
19
INSERT INTO table_time_tz (ts, tstz)
20
VALUES ('2020-12-22 10:10:10',
21
'2020-12-22 10:10:10.009+05:30');
22
​
23
SELECT * FROM table_time_tz;
24
​
25
ts | tstz
26
---------------------+-------------------------------
27
2020-12-22 10:10:10 | 2020-12-22 10:10:10.009+05:30
28
(1 row)
29
​
30
​
31
SELECT CURRENT_TIMESTAMP;
32
​
33
current_timestamp
34
---------------------------------
35
2021-08-12 12:53:29.54762+05:30
36
(1 row)
37
​
38
​
39
SELECT timezone('Asia/Singapore','2020-01-01 00:00:00')
40
​
41
timezone
42
---------------------
43
2020-01-01 02:30:00
44
(1 row)
Copied!

UUID

  • UUID : Universal Unique Identifier
  • PostgreSQL doesn't provide internal function to generate UUID's, use uuid-ossp
1
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2
​
3
SELECT uuid_generate_v1();
4
​
5
uuid_generate_v1
6
--------------------------------------
7
4d459e0c-fb3e-11eb-a638-0242ac110002
8
​
9
​
10
-- pure randomness
11
SELECT uuid_generate_v4();
12
​
13
uuid_generate_v4
14
--------------------------------------
15
418f39e5-8a46-4da2-8cea-884904f45d6f
16
​
17
​
18
CREATE TABLE products_uuid (
19
id uuid default uuid_generate_v1(),
20
product_name varchar(100) not null
21
);
22
​
23
INSERT INTO products_uuid (product_name)
24
VALUES ('ice cream'),('cake'),('candies');
25
​
26
SELECT * FROM products_uuid;
27
​
28
id | product_name
29
--------------------------------------+--------------
30
5cf1dbe0-fb3e-11eb-a638-0242ac110002 | ice cream
31
5cf1df28-fb3e-11eb-a638-0242ac110002 | cake
32
5cf1df46-fb3e-11eb-a638-0242ac110002 | candies
33
​
34
CREATE TABLE products_uuid_v4 (
35
id uuid default uuid_generate_v4(),
36
product_name varchar(100) not null
37
);
38
​
39
INSERT INTO products_uuid_v4 (product_name)
40
VALUES ('ice cream'),('cake'),('candies');
41
​
42
SELECT * FROM products_uuid_v4;
43
​
44
learning=# SELECT * FROM products_uuid_v4;
45
id | product_name
46
--------------------------------------+--------------
47
83b74bed-2cf8-4e26-80b0-c7c7b2e5f3e7 | ice cream
48
ac563251-7a95-408d-966b-ed5ecc1f228d | cake
49
1079f6d3-b0c3-40ef-bd2e-da4467b63432 | candies
Copied!

HSTORE

  • stores data in key-value pairs
  • key and VALUES are text string only
1
CREATE EXTENSION IF NOT EXISTS hstore;
2
​
3
CREATE TABLE table_hstore (
4
id SERIAL PRIMARY KEY ,
5
title varchar(100) not null,
6
book_info hstore
7
);
8
​
9
INSERT INTO table_hstore (title, book_info) VALUES
10
(
11
'Title 1', ' "publisher" => "ABC publisher" ,
12
"paper_cost" => "100" , "e_cost" => "5.85" '
13
);
14
​
15
SELECT * FROM table_hstore;
16
​
17
id | title | book_info
18
​
19
1 | Title 1 | "e_cost"=>"5.85", "publisher"=>"ABC publisher", "paper_cost"=>"100"
20
​
21
​
22
SELECT book_info -> 'publisher' as publisher
23
FROM table_hstore;
24
​
25
publisher
26
---------------
27
ABC publisher
Copied!

Json

  • PostgreSQL supports both
    • JSON
    • BSON or JSONB ( Binary JSON )
  • JSONB has full support for indexing
1
CREATE TABLE table_json (
2
id SERIAL PRIMARY KEY ,
3
docs json
4
);
5
​
6
INSERT INTO table_json (docs)
7
VALUES ('[1,2,3,4,5,6]'),('{"key":"value"}');
8
​
9
INSERT INTO table_json (docs)
10
VALUES ('[{"key":"value"},{"key2":"value2"}]');
11
​
12
SELECT * FROM table_json;
13
​
14
id | docs
15
----+-------------------------------------
16
1 | [1,2,3,4,5,6]
17
2 | {"key":"value"}
18
3 | [{"key":"value"},{"key2":"value2"}]
19
​
20
​
21
ALTER TABLE table_json alter column docs type jsonb;
22
​
23
SELECT * FROM table_json where docs @> '2';
24
​
25
id | docs
26
----+--------------------
27
1 | [1, 2, 3, 4, 5, 6]
28
​
29
​
30
CREATE index on table_json USING GIN (docs jsonb_path_ops);
Copied!

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
1
CREATE TABLE table_netaddr (
2
id SERIAL PRIMARY KEY ,
3
ip inet
4
);
5
​
6
INSERT INTO table_netaddr (ip)
7
VALUES ('148.77.50.74'),
8
('110.158.172.66'),
9
('176.103.251.175'),
10
('84.84.14.58'),
11
('141.122.225.161'),
12
('78.44.113.33'),
13
('81.236.254.9'),
14
('82.116.85.21'),
15
('54.64.79.223'),
16
('162.240.78.253');
17
​
18
SELECT * FROM table_netaddr LIMIT 5;
19
​
20
id | ip
21
----+-----------------
22
1 | 148.77.50.74
23
2 | 110.158.172.66
24
3 | 176.103.251.175
25
4 | 84.84.14.58
26
5 | 141.122.225.161
27
​
28
​
29
SELECT
30
ip,
31
set_masklen(ip,24) as inet_24,
32
set_masklen(ip::cidr,24) as cidr_24 ,
33
set_masklen(ip::cidr,27) as cidr_27,
34
set_masklen(ip::cidr,28) as cidr_28
35
FROM
36
table_netaddr LIMIT 2;
37
​
38
ip | inet_24 | cidr_24 | cidr_27 | cidr_28
39
​
40
148.77.50.74 | 148.77.50.74/24 | 148.77.50.0/24 | 148.77.50.64/27 | 148.77.50.64/28
41
110.158.172.66 | 110.158.172.66/24 | 110.158.172.0/24 | 110.158.172.64/27 | 110.158.172.64/28
Copied!
Last modified 3mo ago