PostgreSQL Data Type List

Reading Time: 2 minutes

Numeric Types

Name Storage Size Description Range
smallint 2 bytes small-range integer -32768 to +32767
integer 4 bytes typical choice for integer -2147483648 to +2147483647
bigint 8 bytes large-range integer -9223372036854775808 to 9223372036854775807
decimal variable user-specified precision,exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric variable user-specified precision,exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real 4 bytes variable-precision,inexact 6 decimal digits precision
double precision 8 bytes variable-precision,inexact 15 decimal digits precision
smallserial 2 bytes small autoincrementing integer 1 to 32767
serial 4 bytes autoincrementing integer 1 to 2147483647
bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

Monetary Types

Name Storage Size Description Range
money 8 bytes currency amount -92233720368547758.08 to +92233720368547758.07

Character Types

S. No. Name & Description
1 character varying(n), varchar(n)

variable-length with limit

2 character(n), char(n)

fixed-length, blank padded

3 text

variable unlimited length

 

 

Binary Data Types

Name Storage Size Description
bytea 1 or 4 bytes plus the actual binary string variable-length binary string

 

Date/Time Types

Name Storage Size Description Low Value High Value
timestamp [(p)] [without time zone ] 8 bytes both date and time (no time zone) 4713 BC 294276 AD
timestamp [(p) ] with time zone 8 bytes both date and time, with time zone 4713 BC 294276 AD
date 4 bytes date (no time of day) 4713 BC 5874897 AD
time [ (p)] [ without time zone ] 8 bytes time of day (no date) 00:00:00 24:00:00
time [ (p)] with time zone 12 bytes times of day only, with time zone 00:00:00+1459 24:00:00-1459
interval [fields ] [(p) ] 12 bytes time interval -178000000 years 178000000 years

 

Boolean Type

Name Storage Size Description
boolean 1 byte state of true or false

 

Geometric Type

Name Storage Size Representation Description
point 16 bytes Point on a plane (x,y)
line 32 bytes Infinite line (not fully implemented) ((x1,y1),(x2,y2))
lseg 32 bytes Finite line segment ((x1,y1),(x2,y2))
box 32 bytes Rectangular box ((x1,y1),(x2,y2))
path 16+16n bytes Closed path (similar to polygon) ((x1,y1),…)
path 16+16n bytes Open path [(x1,y1),…]
polygon 40+16n Polygon (similar to closed path) ((x1,y1),…)
circle 24 bytes Circle <(x,y),r> (center point and radius)

 

Network Address Type

Name Storage Size Description
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

 

Text Search Type

S. No. Name & Description
1 tsvector

This is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as “lexemes”.

2 tsquery

This stores lexemes that are to be searched for, and combines them honoring the Boolean operators & (AND), | (OR), and ! (NOT). Parentheses can be used to enforce grouping of the operators.

 

Leave a Reply

Your email address will not be published. Required fields are marked *