Skip to main content

Datatypes

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

Arbitrary Precision Numbers

The type numeric can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with numeric values yield exact results where possible, e.g. addition, subtraction, multiplication. However, calculations on numeric values are very slow compared to the integer types, or to the floating-point types described in the next section.

We use the following terms below: The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.

Both the maximum precision and the maximum scale of a numeric column can be configured. To declare a column of type numeric use the syntax:

NUMERIC(precision, scale)

Character Types

Name

Description

character varying(n), varchar(n)

variable-length with limit

character(n), char(n)

fixed-length, blank padded

text

variable unlimited length

Date/Time Types

Name

Storage Size

Description

Low Value

High Value

Resolution

timestamp [ (p) ] [ without time zone ]

8 bytes

both date and time (no time zone)

4713 BC

294276 AD

1 microsecond

timestamp [ (p) ] with time zone

8 bytes

both date and time, with time zone

4713 BC

294276 AD

1 microsecond

date

4 bytes

date (no time of day)

4713 BC

5874897 AD

1 day

time [ (p) ] [ without time zone ]

8 bytes

time of day (no date)

00:00:00

24:00:00

1 microsecond

time [ (p) ] with time zone

12 bytes

time of day (no date), with time zone

00:00:00+1459

24:00:00-1459

1 microsecond

interval [ fields ] [ (p) ]

16 bytes

time interval

-178000000 years

178000000 years

1 microsecond

Date Input

Example

Description

1999-01-08

ISO 8601; January 8 in any mode (recommended format)

January 8, 1999

unambiguous in any datestyle input mode

1/8/1999

January 8 in MDY mode; August 1 in DMY mode

1/18/1999

January 18 in MDY mode; rejected in other modes

01/02/03

January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode

1999-Jan-08

January 8 in any mode

Jan-08-1999

January 8 in any mode

08-Jan-1999

January 8 in any mode

99-Jan-08

January 8 in YMD mode, else error

08-Jan-99

January 8, except error in YMD mode

Jan-08-99

January 8, except error in YMD mode

19990108

ISO 8601; January 8, 1999 in any mode

990108

ISO 8601; January 8, 1999 in any mode

1999.008

year and day of year

J2451187

Julian date

 

Example

Description

January 8, 99 BC

year 99 BC

Time Input

Example

Description

04:05:06.789

ISO 8601

04:05:06

ISO 8601

04:05

ISO 8601

040506

ISO 8601

04:05 AM

same as 04:05; AM does not affect value

04:05 PM

same as 16:05; input hour must be <= 12

04:05:06.789-8

ISO 8601

04:05:06-08:00

ISO 8601

04:05-08:00

ISO 8601

040506-08

ISO 8601

04:05:06 PST

time zone specified by abbreviation

2003-04-12 04:05:06 America/New_York

time zone specified by full name

Time Zone Input

Example

Description

PST

Abbreviation (for Pacific Standard Time)

America/New_York

Full time zone name

PST8PDT

POSIX-style time zone specification

-8:00

ISO-8601 offset for PST

-800

ISO-8601 offset for PST

-8

ISO-8601 offset for PST

zulu

Military abbreviation for UTC

z

Short form of zulu

Special Date/Time Inputs

Input String

Valid Types

Description

epoch

date, timestamp

1970-01-01 00:00:00+00 (Unix system time zero)

infinity

date, timestamp

later than all other time stamps

-infinity

date, timestamp

earlier than all other time stamps

now

 date, time, timestamp  current transaction’s start time

today

 date, timestamp  midnight today

yesterday

 date, timestamp midnight yesterday

allballs

 time  00:00:00.00 UTC