PostgreSQL Data Types

www.databasestar.com
Numeric Character

A small integer number CHARACTER A variable-length string up to “n” characters.

SMALLINT
Range -32,768 to +32,767 VARYING (n) Range: Up to 10,485,760 characters (1GB)

INTEGER An integer number VARCHAR (n) A variable-length string up to “n” characters.

Range -2,147,483,648 to +2,147,483,647, Range: Up to 10,485,760 characters (1GB)

BIGINT A large integer number CHARACTER (n) A fixed-length string, padded to a length of “n” characters.
Range -9,223,372,036,854,775,808 to Range: Up to 10,485,760 characters (1GB)
+9,223,372,036,854,775,807

A decimal number with precision. CHAR (n) A fixed-length string, padded to a length of “n” characters.
DECIMAL
Range: up to 131,072 digits before the decimal point; up to Range: Up to 10,485,760 characters (1GB)
16,383 digits after the decimal point
TEXT A variable length string
NUMERIC (p, s) A decimal number with precision of “p” and scale of “s”
Range: up to 131,072 digits before the decimal point; up to BYTEA A variable-length binary string. Similar to BLOB
16,383 digits after the decimal point
ENUM A set of values that can be used for a column.
REAL A floating-point variable-precision number. 6 decimal digits
precision
JSON Stores JSON data
DOUBLE A floating-point variable-precision number. 15 decimal digits
PRECISION precision JSONB Stores JSON data in binary format, and can support
indexing.
SMALLSERIAL A small automatically incrementing integer.
Range: 1 to 32,767

SERIAL An automatically incrementing integer.

Range: 1 to 2,147,483,647 Other
BOOLEAN Stores either true or false.
BIGSERIAL A large automatically incrementing integer.
True, yes, on, 1. False, no off, 0.
Range: 1 to 9,223,372,036,854,775,807

MONEY A currency amount. POINT A point of geometry

Range: -92,233,720,368,547,758.08 to
+92,233,720,368,547,758.07
LINE A line of geometry

LSEG A segment of a line

BOX A rectangular box

Date PATH An open path

POLYGON A polygon or shape

TIMESTAMP (p) A date and time value with no time zone. Precision “p” can be
specified which is the number of fractional seconds.
Range: 4713 BC to 294276 AD CIRCLE A circle

TIMESTAMP (p) A date and time value with time zone. Precision “p” can be CIDR Stores IPv4 and IPv6 network addresses
WITH TIME ZONE specified which is the number of fractional seconds.
Range: 4713 BC to 294276 AD
INET Stores IPv4 and IPv6 hosts and network addresses
DATE A date but no time.
Range: 4713 BC to 5874897 AD MACADDR Stores MAC addresses using 6 bytes.

TIME (p) A time of day with no date Precision “p” can be specified
MACADDR8 Stores MAC addresses using 8 bytes (the EUI-64 format)
which is the number of fractional seconds.
Range: 00:00:00 to 24:00:00
TSVECTOR A sorted list of words
TIME (p) WITH A time of day with no date and a time zone Precision “p” can be
TIME ZONE specified which is the number of fractional seconds. TSQUERY A list of words to be searched for
Range: 00:00:00+1459 to 24:00:00-1459

UUID Stores a Universally Unique Identifier (or GUID). A 128-bit

INTERVAL [fields] An interval of time. Precision “p” can be specified which is the
generated value
(p) number of fractional seconds. The parameter “fields” can be used
to specify the type of data (e.g. YEAR, MONTH, DAY TO HOUR)
Range: -178,000,000 years to 178,000,000 years XML Stores XML data

PG_LSN PostgreSQL Log Sequence Number

TXID_SNAPSHOT A user-level transaction ID snapshot