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

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

 

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