Chapter 7Greenplum Database数据类型
GreenplumDatabase具有丰富的可供用户使用的本地数据类型。用户可以使用create type命令定义新的数据类型。该参考显示了所有内置的数据类型。除了这里列出的类型之外,还有alsosome内部使用的数据类型,如oid(对象标识符),但这些在本指南中没有记录。
以下数据类型由SQL指定:位,位变量,布尔值,字符变化,varchar,字符,字符,日期,双精度,整数,间隔,数字,小数点,实数,小数点,时间(有或没有时区) ,时间戳(有或没有时区)。
每种数据类型都有一个由其输入和输出功能决定的外部表示。许多内置类型都有明显的外部格式。但是,几种类型对于PostgreSQL(和Greenplum数据库)来说都是独特的,比如几何路径,或者对于格式有各种可能性,比如日期和时间类型。一些输入和输出功能是不可逆的。也就是说,与原始输入相比,输出函数的结果可能会失去准确性。
Table 132: Greenplum Database Built-in Data Types
|
|
For variable length data types, if thedata is greater than or equal to 127 bytes, the storage overhead is 4 bytesinstead of 1.
Name | Alias | Size | Range | 描述 |
character varying [(n) ]. | varchar [ (n)] | 1 byte + string size | strings up to n characters in length | variable-length with limit |
cidr |
| 12 or 24 bytes |
| IPv4 and IPv6 networks |
circle |
| 24 bytes | <(x,y),r> (center and radius) | circle in the plane -not allowed in distribution key columns. |
date |
| 4 bytes | 4713 BC - 294,277 AD | calendar date (year, month, day) |
decimal [ (p, s) ] | numeric [ (p, s)] | variable | no limit | user-specified precision, exact |
double precision | float8 float | 8 bytes | 15 decimal digits precision | variable-precision, inexact |
inet |
| 12 or 24 bytes |
| IPv4 and IPv6 hosts and networks |
integer | int, int4 | 4 bytes | -2147483648 to +2147483647 | usual choice for integer |
interval [ (p)] |
| 12 bytes | -178000000 years - 178000000 years | time span |
lseg |
| 32 bytes | ((x1,y1),(x2,y2)) | line segment in the plane - not allowed in distribution key columns. |
macaddr |
| 6 bytes |
| MAC addresses |
money |
| 4 bytes | -21474836.48 to +21474836.47 | currency amount |
path. |
| 16+16n bytes | [(x1,y1),...] | geometric path in the plane - not allowed in distribution key columns. |
point |
| 16 bytes | (x,y) | geometric point in the plane - not allowed in distribution key columns. |
polygon |
| 40+16n bytes | ((x1,y1),...) | closed geometric path in the plane -not allowed in distribution key columns. |
real | float4 | 4 bytes | 6 decimal digits precision | variable-precision, inexact |
|
Name | Alias | Size | Range | 描述 |
serial | serial4 | 4 bytes | 1 to 2147483647 | autoincrementing integer |
smallint | int2 | 2 bytes | -32768 to +32767 | small range integer |
Text . |
| 1 byte + string size | strings of any length | variable unlimited length |
time [ (p) ] [without time zone ] |
| 8 bytes | 00:00:00[.000000]- 24:00:00[.000000] | time of day only |
time [ (p) ] with time zone | timetz | 12 bytes | 00:00:00+1359 - 24:00:00-1359 | time of day only, with time zone |
timestamp [ (p) ] [without time zone ] |
| 8 bytes | 4713 BC - 294,277 AD | both date and time |
timestamp [ (p)] with time zone | timestamptz | 8 bytes | 4713 BC - 294,277 AD | both date and time, with time zone |
Xml . |
| 1 byte + xml size | xml of any length | variable unlimited length |