Chapter 7Greenplum Database数据类型

Chapter 7Greenplum Database数据类型

GreenplumDatabase具有丰富的可供用户使用的本地数据类型。用户可以使用create type命令定义新的数据类型。该参考显示了所有内置的数据类型。除了这里列出的类型之外，还有alsosome内部使用的数据类型，如oid（对象标识符），但这些在本指南中没有记录。
以下数据类型由SQL指定：位，位变量，布尔值，字符变化，varchar，字符，字符，日期，双精度，整数，间隔，数字，小数点，实数，小数点，时间（有或没有时区） ，时间戳（有或没有时区）。
每种数据类型都有一个由其输入和输出功能决定的外部表示。许多内置类型都有明显的外部格式。但是，几种类型对于PostgreSQL（和Greenplum数据库）来说都是独特的，比如几何路径，或者对于格式有各种可能性，比如日期和时间类型。一些输入和输出功能是不可逆的。也就是说，与原始输入相比，输出函数的结果可能会失去准确性。

Table 132: Greenplum Database Built-in Data Types

Name

Alias

Size

Range

描述

bigint

int8

8 bytes

-9223372036854775808 to 9223372036854775807

large range integer

bigserial

serial8

8 bytes

1 to 9223372036854775807

large

autoincrementing

integer

bit [ (n)]

 

n bits

bit string constant

fixed-length bit string

bit varying [(n) ]

varbit

actual number of bits

bit string constant

variable-length bit string

boolean

bool

1 byte

true/false, t/f, yes/ no, y/n, 1/0

logical boolean (true/false)

box

 

32 bytes

((x1,y1),(x2,y2))

rectangular box in the plane - not allowed in distribution key columns.

byteaFootnote.

 

1 byte + binary string

sequence of octets

variable-length binary string

character [(n) ]

char [ (n)]

1 byte + n

strings up to n characters in length

fixed-length, blank padded

 

 

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 [phone]

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