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 |