AnalyticDB for PostgreSQL supports the data types listed below. To define a custom type, run a CREATE TYPE statement.
Supported data types
Types are grouped by category. Each entry shows its alias, storage size, value range, and a short description.
Numeric types
Use integer for most whole-number columns — it balances range, storage, and performance. Use smallint only when storage is a hard constraint. Use bigint when values exceed the integer range. For exact decimal arithmetic (for example, financial calculations), use decimal/numeric rather than double precision or real, which are inexact floating-point types.
| Data type |
Alias |
Length |
Range |
Description |
| smallint |
int2 |
2 bytes |
-32768 to +32767 |
Small-range integer. |
| integer |
int, int4 |
4 bytes |
-2,147,483,648 to +2,147,483,647 |
Standard integer. |
| bigint |
int8 |
8 bytes |
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
Large-range integer. |
| decimal [ (p, s) ] |
numeric [ (p, s) ] |
Variable |
Unlimited |
Exact user-specified precision. |
| real |
float4 |
4 bytes |
6 significant digits |
Inexact, variable-precision floating-point. |
| double precision |
float8 |
8 bytes |
15 significant digits |
Inexact, variable-precision floating-point. |
| float |
| money |
|
8 bytes |
-92,233,720,368,547,758.08 to +92,233,720,368,547,758.07 |
Monetary amount. |
Serial (auto-increment) types
| Data type |
Alias |
Length |
Range |
Description |
| serial |
serial4 |
4 bytes |
1 to 2,147,483,647 |
Auto-incrementing integer. |
| bigserial |
serial8 |
8 bytes |
1 to 9,223,372,036,854,775,807 |
Auto-incrementing large integer. |
Character types
Use text or varchar for most text columns. Use char(n) only when a fixed-width representation is required by an external system — it pads shorter values with spaces.
| Data type |
Alias |
Length |
Range |
Description |
| character [ (n) ] |
char [ (n) ] |
1 byte + n |
Up to n characters |
Fixed-length, blank-padded string. |
| character varying [ (n) ] |
varchar [ (n) ] |
1 byte + string size |
Up to n characters |
Variable-length string with an upper limit. |
| text |
|
1 byte + string size |
Unlimited |
Variable-length string with no limit. |
Binary types
| Data type |
Alias |
Length |
Range |
Description |
| bytea |
|
1 byte + binary string |
1 or 4 bytes plus the actual binary string |
Variable-length binary string. |
Date and time types
| Data type |
Alias |
Length |
Range |
Description |
| date |
|
4 bytes |
4713 BC to 294,277 AD |
Calendar date (year, month, day). |
| time [ (p) ] [ without time zone ] |
|
8 bytes |
00:00:00[.000000] to 24:00:00[.000000] |
Time of day, no time zone. |
| time [ (p) ] with time zone |
timetz |
12 bytes |
00:00:00+1359 to 24:00:00-1359 |
Time of day with time zone. |
| timestamp [ (p) ] [ without time zone ] |
|
8 bytes |
4713 BC to 294,277 AD |
Date and time, no time zone. |
| timestamp [ (p) ] with time zone |
timestamptz |
8 bytes |
4713 BC to 294,277 AD |
Date and time with time zone. |
| interval [ (p) ] |
|
12 bytes |
-178,000,000 to 178,000,000 years |
Time span. |
Boolean type
| Data type |
Alias |
Length |
Range |
Description |
| boolean |
bool |
1 byte |
true/false, t/f, yes/no, y/n, 1/0 |
Logical Boolean value. |
Bit string types
| Data type |
Alias |
Length |
Range |
Description |
| bit [ (n) ] |
|
n bits |
Bit string constant |
Fixed-length bit string. |
| bit varying [ (n) ] |
varbit |
Variable |
Bit string constant |
Variable-length bit string. |
Network address types
| Data type |
Alias |
Length |
Description |
| cidr |
|
12 or 24 bytes |
IPv4 and IPv6 networks. |
| inet |
|
12 or 24 bytes |
IPv4 and IPv6 hosts and networks. |
| macaddr |
|
6 bytes |
MAC address. |
Text search and document types
| Data type |
Alias |
Length |
Range |
Description |
| json |
|
1 byte + JSON size |
JSON string |
A string with an unlimited variable length. |
| xml |
|
1 byte + XML size |
Unlimited |
A string with an unlimited variable length. |
UUID type
| Data type |
Alias |
Length |
Description |
| uuid |
|
32 bytes |
Universally unique identifier. |
uuid is built into AnalyticDB for PostgreSQL V6.0. On V4.3, install the uuid-ossp extension before using this type. For details, see Use the uuid-ossp extension.
Geometric types
Geometric types (box, circle, lseg, path, point, polygon) cannot be used as distribution key columns.
| Data type |
Alias |
Length |
Range |
Description |
| box |
|
32 bytes |
((x1,y1),(x2,y2)) |
Rectangular box on a plane. |
| circle |
|
24 bytes |
<(x,y),r> (center and radius) |
Circle on a plane. |
| lseg |
|
32 bytes |
((x1,y1),(x2,y2)) |
Line segment on a plane. |
| path |
|
16+16n bytes |
[(x1,y1),...] |
Geometric path on a plane. |
| point |
|
16 bytes |
(x,y) |
Geometric point on a plane. |
| polygon |
|
40+16n bytes |
((x1,y1),...) |
Closed geometric path on a plane. |