Data Types

Greenplum Database has a rich set of native data types available to users. Users may also define new data types using the CREATE TYPE command. This reference shows all of the built-in data types. In addition to the types listed here, there are also some internally used data types, such as oid (object identifier), but those are not documented in this guide.

Additional modules that you register may also install new data types. The hstore module, for example, introduces a new data type and associated functions for working with key-value pairs. See hstore. The citext module adds a case-insensitive text data type. See citext.

The following data types are specified by SQL: bit, bit varying, boolean, character varying, varchar, character, char, date, double precision, integer, interval, numeric, decimal, real, smallint, time (with or without time zone), and timestamp (with or without time zone).

Each data type has an external representation determined by its input and output functions. Many of the built-in types have obvious external formats. However, several types are either unique to PostgreSQL (and Greenplum Database), such as geometric paths, or have several possibilities for formats, such as the date and time types. Some of the input and output functions are not invertible. That is, the result of an output function may lose accuracy when compared to the original input.

Table 1. Greenplum Database Built-in Data Types
NameAliasSizeRangeDescription
bigintint88 bytes-922337203​6854775808 to 922337203​6854775807large range integer
bigserialserial88 bytes1 to 922337203​6854775807large autoincrementing integer
bit [ (n) ] n bitsbit string constantfixed-length bit string
bit varying [ (n) ]1varbitactual number of bitsbit string constantvariable-length bit string
booleanbool1 bytetrue/false, t/f, yes/no, y/n, 1/0logical boolean (true/false)
box 32 bytes((x1,y1),(x2,y2))rectangular box in the plane - not allowed in distribution key columns.
bytea1 1 byte + binary stringsequence of octetsvariable-length binary string
character [ (n) ]1char [ (n) ]1 byte + nstrings up to n characters in lengthfixed-length, blank padded
character varying [ (n) ]1varchar [ (n) ]1 byte + string sizestrings up to n characters in lengthvariable-length with limit
cidr 12 or 24 bytes IPv4 and IPv6 networks
circle 24 bytes<(x,y),r> (center and radius)circle in the plane - not allowed in distribution key columns.
date 4 bytes4713 BC - 294,277 ADcalendar date (year, month, day)
decimal [ (p, s) ]1numeric [ (p, s) ]variableno limituser-specified precision, exact
double precisionfloat8

float

8 bytes15 decimal digits precisionvariable-precision, inexact
inet 12 or 24 bytes IPv4 and IPv6 hosts and networks
integerint, int44 bytes-2147483648 to +2147483647usual choice for integer
interval [ fields ] [ (p) ] 16 bytes-178000000 years to 178000000 yearstime span
json 1 byte + json sizejson of any lengthvariable unlimited length
jsonb 1 byte + binary stringjson of any length in a decomposed binary formatvariable unlimited length
lseg 32 bytes((x1,y1),(x2,y2))line segment in the plane - not allowed in distribution key columns.
macaddr 6 bytes MAC addresses
money 8 bytes-92233720368547758.08 to +92233720368547758.07currency amount
path1 16+16n bytes[(x1,y1),…]geometric path in the plane - not allowed in distribution key columns.
point 16 bytes(x,y)geometric point in the plane - not allowed in distribution key columns.
polygon 40+16n bytes((x1,y1),…)closed geometric path in the plane - not allowed in distribution key columns.
realfloat44 bytes6 decimal digits precisionvariable-precision, inexact
serialserial44 bytes1 to 2147483647autoincrementing integer
smallintint22 bytes-32768 to +32767small range integer
text1 1 byte + string sizestrings of any lengthvariable unlimited length
time [ (p) ] [ without time zone ] 8 bytes00:00:00[.000000] - 24:00:00[.000000]time of day only
time [ (p) ] with time zonetimetz12 bytes00:00:00+1359 - 24:00:00-1359time of day only, with time zone
timestamp [ (p) ] [ without time zone ] 8 bytes4713 BC - 294,277 ADboth date and time
timestamp [ (p) ] with time zonetimestamptz8 bytes4713 BC - 294,277 ADboth date and time, with time zone
uuid 16 bytes Universally Unique Identifiers according to RFC 4122, ISO/IEC 9834-8:2005
xml1 1 byte + xml sizexml of any lengthvariable unlimited length
txid_snapshot   user-level transaction ID snapshot

Parent topic: Greenplum Database Reference Guide

1 For variable length data types, if the data is greater than or equal to 127 bytes, the storage overhead is 4 bytes instead of 1.

Need help? Visit the Greenplum Database Community