《GreenPlum系列》GreenPlum初级教程-04GreenPlum数据类型

第四章 GreenPlum数据类型

1.基本数据类型

1.1 数值类型

类型名称	存储空间	描述	范围
smallint	2字节	小范围整数	-32768 ~ 32767
integer	4字节	常用的整数	-2147483648~+2147483647
bigint	8字节	大范围的整数	-9223372036 854 ~9223372036854
decimal	变长	用户声明精度，精确	无限制
numeric	变长	用户声明精度，精确	无限制
real	4字节	变精度，不精确	6位十进制数字精度
double precision	8字节	变精度，不精确	15位十进制数字精度
serial	4字节	自增整数	1 - 2147483 647
bigserial	8字节	大范围的自增整数	1 - 9223372036854775807
smallserial	字节	小范围的自增整数	1 - 32767

1.2 字符类型

类型名称	描述
character varying(n),varchar(n)	变长，有长度限制
character(n),char(n)	定长，不足补空白
text	变长，无长度限制

1.3 时间类型

类型名称	存储空间	描述	最低值	最高值	时间精度
timestamp[§][without time zone]	8字节	日期和时间	4713BC	5874897AD	1毫秒
timestamp[§] with time zone	8字节	日期和时间，带时区	4713BC	5874897AD	1毫秒
interval[§]	12字节	时间间隔	-178 000 000年	178 000 000年	1毫秒
date	4字节	只用于表示日期	4713BC	5 874 897AD	1天
time[§][without time zone]	8字节	只用于表示一日内的时间	00:00:00	24:00:00	1毫秒
time[§] with time zone	12字节	只用于表示一日内时间，带时区	00:00:00+1459	24:00:00-1459	1毫秒

2.复杂数据类型

2.1 枚举类型

枚举类型是一个包含静态和值的有序集合的数据类型，类似于Java中的enum类型，需要使用create type命令创建。

1）创建枚举类型

zxy=# create type ods.enumtype as enum('Mon','Tue','Wed','Thu','Fri','Sat','Sun');
CREATE TYPE

2）创建表引用枚举

zxy=# create table ods.enum_table(
zxy(# id int,
zxy(# week ods.enumtype
zxy(# );

3）插入数据

zxy=# insert into ods.enum_table values(1,'Mon');
INSERT 0 1

4）查询数据

zxy=# select * from ods.enum_table;
 id | week
----+------
  1 | Mon
(1 row)

2.2 几何类型

几何类型标识二维的平面物体，如下列出GreenPlum支持的几何类型：

类型	大小	描述	表现形式
point	16字节	平面中的点	(x,y)
line	32字节	直线	((x1,y1),(x2,y2))
lseg	32字节	线段	((x1,y1),(x2,y2))
box	32字节	矩形	((x1,y1),(x2,y2))
path	16+16n字节	路径（与多边形相似）	((x1,y1),…)
polygon	40+16n字节	多边形	((x1,y1),…)
circle	24字节	圆	<(x,y),r> (圆心和半径)

1）创建表，存储点

zxy=# create table ods.point_table(
zxy(# id serial primary key,
zxy(# point1 point
zxy(# );
CREATE TABLE

2）插入数据

zxy=# insert into ods.point_table(point1) values(point(1,2));
INSERT 0 1

3）查询数据

zxy=# select * from ods.point_table;
 id | point1
----+--------
  1 | (1,2)
(1 row)

2.3 网络地址类型

GreenPlum提供用户存储IPV4、IPV6、MAC地址的数据类型。用这些数据类型存储网络地址比用纯文本好，因为提供输入错误检查和特殊的操作和功能。

类型	描述	说明
cidr	7或19字节	IPv4 或 IPv6 网络
inet	7或19字节	IPv4 或 IPv6 主机和网络
macaddr	6字节	MAC 地址

1）创建表，存储网络地址

zxy=# create table ods.network_table (
zxy(# id serial primary key,
zxy(# network cidr,
zxy(# ipaddress inet,
zxy(# macaddress macaddr
zxy(# );
CREATE TABLE

2）插入数据

zxy=# insert into ods.network_table(network,ipaddress,macaddress) values('192.168.1.1','192.168.1.0/24','07:01:2b:03:02:03');
INSERT 0 1

3）查询数据

zxy=# select * from ods.network_table;
 id |    network     |   ipaddress    |    macaddress
----+----------------+----------------+-------------------
  1 | 192.168.1.1/32 | 192.168.1.0/24 | 07:01:2b:03:02:03

2.4 JOSN类型

json数据类型可以用来JSON(JavaScript Object Notation)数据，这样的数据也可以存储为text，但是json数据类型更有利于检查每个存储的数值是可用的JSON值。

1）创建表，存储Json数据

zxy=# create table ods.json_table(
zxy(# id serial primary key,
zxy(# data json
zxy(# );
CREATE TABLE

2）插入Json数据

zxy=# insert into ods.json_table(data) values('{"name":"zxy","age":18,"city":"SH"}');
INSERT 0 1

3）查询数据

zxy=# select * from ods.json_table;
 id |                data
----+-------------------------------------
  1 | {"name":"zxy","age":18,"city":"SH"}
(1 row)

4）提取json数据

zxy=# select data ->>'name' as name from ods.json_table;
 name
------
 zxy
(1 row)

2.5 数组类型

GreenPlum允许将字段定义成变长的多为的数组，数组可以是任何基本类型或用户定义类型，枚举类型或复合类型。

1）创建表，存储数组

zxy=# create table ods.array_table(
zxy(# id serial primary key,
zxy(# numbers int[]
zxy(# );
CREATE TABLE

2）向数组中插入数据，数组使用{}，使用逗号分隔

zxy=# insert into ods.array_table(numbers) values ('{1,2,3,4,5}');
INSERT 0 1

3）查询数据

zxy=# select * from ods.array_table;
 id |   numbers
----+-------------
  1 | {1,2,3,4,5}
(1 row)

4）根据数组下标取数组数据

zxy=# select numbers[1] from ods.array_table;
 numbers
---------
       1
(1 row)

5）使用unnest函数展开数组列

zxy=# select unnest(numbers) from ods.array_table;
 unnest
--------
      1
      2
      3
      4
      5
(5 rows)

2.6 复合类型

复合类型表示一行或者一条记录的结构；它实际上只是一个字段名和他们的数据类型的列表。GreenPlum允许像简单数据类型那样使用复合类型。比如，一个表的某个字段可以声明为一个复合类型。

定义复合类型，语法类似于create table，只是这里可以声明字段名字和类型。

1）创建一个复合类型表

zxy=# create table ods.complex_type(
zxy(# name text,
zxy(# age int,
zxy(# city text
zxy(# );
CREATE TABLE

2）创建表，存储复合类型

zxy=# create table ods.complex_table(
zxy(# id serial primary key,
zxy(# complex_column ods.complex_type
zxy(# );
CREATE TABLE

3）插入复合数据

zxy=# insert into ods.complex_table(complex_column) values (ROW('zxy',18,'SH'));
INSERT 0 1

4）查询数据

zxy=# select * from ods.complex_table;
 id | complex_column
----+----------------
  1 | (zxy,18,SH)
(1 row)

2.7 位串类型

位串是1和0的字符串。可用于存储和可视化掩码。有两种SQL位类型，【bit(n)】、【bit varying(n)】。

bit类型数据必须与长度n完全匹配；尝试存储较短或者较长的位串都是错误的。

bit varying类型数据是可变长度的，直到最大长度n;较长的字符串将被拒接，没有指定长度的bit，将被认为等同于Bit(1)。而bit varying没有指定长度，就被认为是无限长。

1）创建表，存储位串类型

zxy=# create table ods.bit_table(
zxy(# a bit(3),
zxy(# b bit varying(5)
zxy(# );
CREATE TABLE

2）插入数据

# 插入字段a大小为3，符合条件
# 插入字段b大小为3，符合条件
zxy=# insert into ods.bit_table values(B'100',B'100');
INSERT 0 1

# 插入字段a大小为2，不符合条件
# 插入字段b大小为3，符合条件
zxy=# insert into ods.bit_table values(B'10',B'100');
ERROR:  bit string length 2 does not match type bit(3)

# 插入字段a大小为3，符合条件
# 插入字段b大小为6，不符合条件
zxy=# insert into ods.bit_table values(B'100',B'100000');
ERROR:  bit string too long for type bit varying(5)