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 |
varchar, 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 |