Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Data types

This topic lists the data types available in Greengage DB.

Built-in types

Greengage DB provides a variety of built-in data types for storing different values and objects. Most of these types originate from PostgreSQL and include data types defined by the SQL standard, such as bigint, bit, bit varying, boolean, character varying, varchar, character, char, date, double precision, integer, interval, numeric, decimal, real, smallint, time (with or without time zone), timestamp (with or without time zone), and xml. For detailed semantics and behavior of standard data types, see the Data types section in the PostgreSQL documentation.

In addition to user-visible data types, Greengage DB defines internal system types, such as oid. These types are typically used by the system catalogs and are generally not intended for direct application use.

Extension types

Greengage DB extensions can introduce additional data types that are not part of the core system. Such types are typically designed to support specialized functionality that would be impractical or inefficient to implement using standard SQL types.

Examples include:

  • The citext extension, which adds the citext type for case-insensitive character strings.

  • The hstore extension, which adds the hstore type for storing sets of key/value pairs.

Custom types

Users can define custom data types using the CREATE TYPE statement. Custom types allow modeling domain-specific data more precisely, encapsulating validation rules, or representing complex structures that are not covered by built-in types.

Custom types can be implemented as composite types, enumerated types, or fully user-defined base types. Their behavior and storage characteristics depend on the implementation method.

Data types list

NOTE

  • Aliases are alternative names for data types that are not defined by the SQL standard. Their usage may reduce portability across different database systems.

  • Detailed information about data types is available in the pg_type system catalog table.

Numeric types

NOTE

The serial, bigserial, and smallserial types are not actual types with their own specific implementation. Technically, columns of these types store values of corresponding integer types and use sequences that produce the values in serial order.

Name Alias Size Range Description

bigint

int8

8 bytes

-9223372036854775808 to 9223372036854775807

Large-range integer

bigserial

serial8

8 bytes

1 to 9223372036854775807

Large autoincrementing integer

decimal[(p, s)]

numeric[(p, s)]

Variable

No limit

Exact numeric value with user-defined precision. Up to 131072 digits before the decimal point and 16383 digits after it

double precision

float8

8 bytes

15 decimal digits precision

Double-precision floating-point number (inexact)

integer

int, int4

4 bytes

-2147483648 to +2147483647

Standard integer type

real

float4

4 bytes

6 decimal digits precision

Single-precision floating-point number (inexact)

serial

serial4

4 bytes

1 to 2147483647

Autoincrementing integer

smallint

int2

2 bytes

-32768 to +32767

Small-range integer

smallserial

serial2

2 bytes

1 to 32767

Small autoincrementing integer

Boolean

Name Alias Size Range Description

boolean

bool

1 byte

  • true, false

  • t, f

  • yes, no

  • y, n

  • 1, 0

Logical boolean value

Character types

NOTE

  • The maximum allowed length (n) for character types is 10485760.

  • Values of 127 bytes or larger require four bytes of overhead instead of one.

Name Alias Size Range Description

character[(n)]

char[(n)]

1 byte + n

Strings up to n characters

Fixed-length, blank-padded character string. Without n specified, equivalent to char(1)

character varying[(n)]

varchar[(n)]

1 byte + string size

Strings up to n characters

Variable-length character string with length limit. Without n specified, accepts strings of any length

text

 — 

1 byte + string size

Strings of any length

Variable-length character string

Binary type

Name Size Range Description

bytea

1 or 4 bytes + binary string size

Sequence of octets

Variable-length binary string (byte array)

Date and time types

NOTE

The p variable in type definitions is the optional precision specification. It defines the number of fractional digits stored in the seconds field. For example, the value of 3 allows storing values with millisecond precision. The allowed range for p is 0 to 6 (or 0 to 10 for the time type when it uses floating-point storage).

Name Alias Size Range Description

date

 — 

4 bytes

4713 BC to 5874897 AD

Calendar date (year, month, day)

interval[fields][(p)]

 — 

16 bytes

-178000000 years to 178000000 years

Time interval

time[(p)][without time zone]

 — 

8 bytes

00:00:00[.000000] to 24:00:00[.000000]

Time of day

time[(p)] with time zone

timetz

12 bytes

00:00:00+1459 to 24:00:00-1459

Time of day with time zone

timestamp[(p)][without time zone]

 — 

8 bytes

4713 BC to 294276 AD

Date and time

timestamp[(p)] with time zone

timestamptz

8 bytes

4713 BC to 294276 AD

Date and time with time zone

Monetary type

Name Size Range Description

money

8 bytes

-92233720368547758.08 to +92233720368547758.07

Currency amount

Geometric types

NOTE

Columns of geometric types cannot be used in distribution keys.

Name Size Range Description

box

32 bytes

((x1,y1),(x2,y2))

Rectangular box on a plane

circle

24 bytes

<(x,y),r>

Circle in the plane, defined by a center point and a radius

line

24 bytes

{A,B,C}

Infinite line on a plane

lseg

32 bytes

((x1,y1),(x2,y2))

Line segment on a plane

path

16 + 16n bytes

[(x1,y1),…​]

((x1,y1),…​)

Geometric path on a plane: open (square brackets) or closed (parentheses)

point

16 bytes

(x,y)

Geometric point on a plane

polygon

40 + 16n bytes

((x1,y1),…​)

Polygon on a plane (similar to closed geometric path)

Network address types

Name Size Description

cidr

7 or 19 bytes

IPv4 and IPv6 network addresses

inet

7 or 19 bytes

IPv4 and IPv6 host and network addresses

macaddr

6 bytes

MAC address

Bit string types

Name Alias Size Range Description

bit[(n)]

 — 

n bits

Bit-string constant

Fixed-length bit string

bit varying[(n)]

varbit

Actual number of bits

Bit-string constant

Variable-length bit string

Text search types

NOTE

Columns of text search types cannot be used in distribution keys.

Name Range Description

tsquery

Set of lexemes to search combined with logical operators

Text search query

tsvector

Sorted list of distinct lexemes

Text search document

Structured data formats: XML and JSON

Name Size Range Description

json

1 byte + JSON size

JSON of any length

JSON data stored as text

jsonb

1 byte + binary string size

JSON of any length

Binary JSON data in decomposed format

xml

1 byte + XML size

XML of any length

XML data

UUID

Name Size Range Description

uuid

16 bytes

Standard UUID format (32 hexadecimal digits)

Universally unique identifier (RFC 4122)

Range types

Name Range Description

int4range

  • [x, y]

  • (x,y)

  • [x,y)

  • (x,y]

NOTE

  • Parenthesis ((, )) means an exclusive bound.

  • Square bracket ([, ]) means an inclusive bound.

Range of integer

int8range

Range of bigint

numrange

Range of numeric

tsrange

Range of timestamp without time zone

tstzrange

Range of timestamp with time zone

daterange

Range of date

Other types

Name Size Range Description

pg_lsn

8 bytes

pg_lsn type

PostgreSQL Log Sequence Number

txid_snapshot

 — 

xmin:xmax:xip_list

Transaction ID snapshot
Previous
VALUES
Next
Server configuration parameters (GUC)