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. |