GBASE 8C——SQL参考1 数据类型

1. 1. 数据类型

GBase 8c数据库具有丰富的数据类型，并且用户可以使用CREATE TYPE命令增加自定义数据类型。

数据库支持的数据类型如下：

名字	别名	描述
bigint	int8	有符号的8字节整数
bigserial	serial8	自动增长的8字节整数
bit [ (n) ]		定长位串
bit varying [ (n) ]	varbit	变长位串
boolean	bool	逻辑布尔值（真/假）
box		平面上的普通方框
bytea		二进制数据（“字节数组”）
character [ (n) ]	char [ (n) ]	定长字符串
character varying [ (n) ]	varchar [ (n) ]	变长字符串
cidr		IPv4或IPv6网络地址
circle		平面上的圆
date		日历日期（年、月、日）
double precision	float8	双精度浮点数（8字节）
inet		IPv4或IPv6主机地址
integer	int, int4	有符号4字节整数
interval [ fields ] [ (p) ]		时间段
json		文本 JSON 数据
jsonb		二进制 JSON 数据，已分解
line		平面上的无限长的线
lseg		平面上的线段
macaddr		MAC（Media Access Control）地址
macaddr8		MAC (Media Access Control) 地址 (EUI-64 格式)
money		货币数量
numeric [ (p, s) ]	decimal [ (p, s) ]	可选择精度的精确数字
path		平面上的几何路径
pg_lsn		日志序列号
point		平面上的几何点
polygon		平面上的封闭几何路径
real	float4	单精度浮点数（4字节）
smallint	int2	有符号2字节整数
smallserial	serial2	自动增长的2字节整数
serial	serial4	自动增长的4字节整数
text		变长字符串
time [ (p) ] [ without time zone ]		一天中的时间（无时区）
time [ (p) ] with time zone	timetz	一天中的时间，包括时区
timestamp [ (p) ] [ without time zone ]		日期和时间（无时区）
timestamp [ (p) ] with time zone	timestamptz	日期和时间，包括时区
tsquery		文本搜索查询
tsvector		文本搜索文档
txid_snapshot		用户级别事务ID快照
uuid		通用唯一标识码
xml		XML数据

1. 1. 1. 数字类型

数字类型分为整数类型、任意精度数字类型、浮点类型和序数类型，GBase 8c数据库支持的所有数字类型如下：

名字	存储尺寸	描述	范围
smallint	2字节	小范围整数	-32768 to +32767
integer	4字节	整数的典型选择	-2147483648 to +2147483647
bigint	8字节	大范围整数	-9223372036854775808 to +9223372036854775807
decimal	可变	用户指定精度，精确	最高小数点前131072位，以及小数点后16383位
numeric	可变	用户指定精度，精确	最高小数点前131072位，以及小数点后16383位
real	4字节	可变精度，不精确	6位十进制精度
double precision	8字节	可变精度，不精确	15位十进制精度
smallserial	2字节	自动增加的小整数	1到32767
serial	4字节	自动增加的整数	1到2147483647
bigserial	8字节	自动增长的大整数	1到9223372036854775807

1. 1. 1. 货币类型

money类型存储固定小数精读的货币数字，精读由lc_monetary参数设置。

名字	存储尺寸	描述	范围
money	8 bytes	货币额	-92233720368547758.08到+92233720368547758.07

1. 1. 1. 字符类型

名字	描述
character varying(n), varchar(n)	有限制的变长
character(n), char(n)	定长，空格填充
text	无限变长

其中，n为正整数。

GBase 8c里定义了两种特殊字符类型：“char”和name。“char”只占用1个字节的存储空间，在系统目录中做简单的枚举类型使用；name类型只用于在系统目录中存储标识符，并不是给一般用户使用的。

名字	存储尺寸	描述
"char"	1字节	单字节内部类型
name	64字节	用于对象名的内部类型

1. 1. 1. 二进制数据类型

bytea类型允许存储二进制串。

名字	存储尺寸	描述
bytea	1或4字节外加真正的二进制串	变长二进制串

butes转义是将将无法用ASCII字符表示的字节转换成特殊语义的语句。如下表内为必须被转义的字符：

十进制字节值	描述	转义输入表示	例子	输出表示
0	0字节	E'\\000'	SELECT E'\\000'::bytea;	\000
39	单引号	''''或E'\\047'	SELECT E'\''::bytea;	'
92	反斜线	E'\\\\'或E'\\134'	SELECT E'\\\\'::bytea;	\\
0到31和127到255	“不可打印的”字节	E'\\xxx'（八进制值）	SELECT E'\\001'::bytea;	\001

同时butes转义也会发生在输出时，如下为输出转义字节表：

十进制字节值	描述	转义的输出表示	例子	输出结果
92	反斜线	\\	SELECT E'\\134'::bytea;	\\
0到31和127到255	“不可打印的”字节	\xxx（八进制值）	SELECT E'\\001'::bytea;	\001
32到126	“可打印的”字节	客户端字符集表示	SELECT E'\\176'::bytea;	~

1. 1. 1. 日期和时间类型

GBase 8c支持所有的SQL日期和时间类型，如下表所示：

名字	存储尺寸	描述	最小值	最大值	解析度
timestamp [ (p) ] [ without time zone ]	8字节	包括日期和时间（无时区）	4713 BC	294276 AD	1微秒 / 14位
timestamp [ (p) ] with time zone	8字节	包括日期和时间，有时区	4713 BC	294276 AD	1微秒 / 14位
date	4字节	日期（没有一天中的时间）	4713 BC	5874897 AD	1日
time [ (p) ] [ without time zone ]	8字节	一天中的时间（无日期）	00:00:00	24:00:00	1微秒 / 14位
time [ (p) ] with time zone	12字节	一天中的时间（不带日期），带有时区	00:00:00+1459	24:00:00-1459	1微秒 / 14位
interval [ fields ] [ (p) ]	16字节	时间间隔	-178000000年	178000000年	1微秒 / 14位

time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界，p允许的范围是从 0 到 6。

1. 1. 1. 布尔类型

boolean的状态可以是：：“true（真）”、“false（假）”和 “unknown（未知）”，未知状态表示为空值。

名字	存储字节	描述
boolean	1字节	状态为真或假

1. 1. 1. 枚举类型

枚举（enum）类型是一个静态、有序的集合构成的数据类型。可以使用CREATE TYPE命令创建，例如：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TYPE

一个枚举值在磁盘上占4字节，其文本标签的长度受限于NAMEDATALEN设置（最多63字节）。枚举标签大小写敏感，空白敏感。

1. 1. 1. 几何类型

几何数据类型表示二维的空间物体，类型如下：

名字	存储尺寸	表示	描述
point	16字节	平面上的点	(x,y)
line	32字节	无限长的线	{A,B,C}
lseg	32字节	有限线段	((x1,y1),(x2,y2))
box	32字节	矩形框	((x1,y1),(x2,y2))
path	16+16n字节	封闭路径（类似于多边形）	((x1,y1),...)
path	16+16n字节	开放路径	[(x1,y1),...]
polygon	40+16n字节	多边形（类似于封闭路径）	((x1,y1),...)
circle	24字节	圆	<(x,y),r> (center point and radius)

1. 1. 1. 网络地址类型

GBase 8c提供用于存储 IPv4、IPv6 和 MAC 地址的数据类型，如下表所示：

名字	存储尺寸	描述
cidr	7或19字节	IPv4和IPv6网络
inet	7或19字节	IPv4和IPv6主机以及网络
macaddr	6字节	MAC地址
macaddr8	8 字节	MAC 地址 (EUI-64 格式)

用这些数据类型存储网络地址，可以进行输入错误检查报错及特殊操作符支持。

1. 1. 1. 位串类型

位串就是一串 1 和 0 的串，可以用于存储和可视化位掩码。GBase 8c提供两种类型的 SQL 位类型：bit(n)和bit varying(n)，其中 n是一个正整数。下面是一个使用位串类型的例子：

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TYPE

CREATE TABLE test (a BIT(3), b BIT VARYING(5));

CREATE TABLE

INSERT INTO test VALUES (B'101', B'00');

INSERT 0 1

INSERT INTO test VALUES (B'10', B'101');

ERROR:  bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');

INSERT 0 1

SELECT * FROM test;

  a  |  b

-----+-----

 101 | 00

 100 | 101

(2 rows)

1. 1. 1. 文本搜索类型

GBase 8c提供两种数据类型支持全文搜索：tsvector类型和tsquery类型。tsvector类型表示一个为文本搜索优化的形式下的文档，tsquery类型表示一个文本查询。详见全文检索。

1. 1. 1. UUID类型

数据类型uuid存储由RFC 4122、ISO/IEC 9834-8:2005以及相关标准定义的通用唯一标识符（UUID）（某些系统将这种数据类型引用为全局唯一标识符GUID）。UUID因为其算法特殊性，很难发生重复，常被用于分布式系统唯一性标识符。标准的UUID类似于：

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

以及：

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11

1. 1. 1. XML类型

XML数据类型可以被用来存储XML数据。

创建XML值语法：

XMLPARSE ( { DOCUMENT | CONTENT } value)

从xml生成字符串命令语法：

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

1. 1. 1. JSON类型

JSON 数据类型是用来存储JSON（JavaScript Object Notation）数据的。JSON数据类型有两种：json和jsonb。json数据类型存储输入文本的精准拷贝，处理函数必须在每次执行时必须重新解析该数据。而jsonb数据存储在二进制格式中，因为需要做附加的转换，jsonb输入速度比json慢，处理速度会快（因为不需要做解析）。同时jsonb还支持索引。

JSON数据类型输入/输出语法：

-- 简单标量/基本值

-- 基本值可以是数字、带引号的字符串、true、false或者null

SELECT '5'::json;

json

------

 5

(1 row)

-- 有零个或者更多元素的数组（元素不需要为同一类型）

SELECT '[1, 2, "foo", null]'::json;

        json

---------------------

 [1, 2, "foo", null]

(1 row)

-- 包含键值对的对象

-- 注意对象键必须总是带引号的字符串

SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

                       json

--------------------------------------------------

 {"bar": "baz", "balance": 7.77, "active": false}

(1 row)

-- 数组和对象可以被任意嵌套

SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

                        json

-----------------------------------------------------

 {"foo": [true, "bar"], "tags": {"a": 1, "b": null}}

(1 row)

1. 1. 1. 数组

GBase 8c允许定义变长多维数组，例如：

CREATE TABLE sal_emp (

    name            text,

    pay_by_quarter  integer[],

    schedule        text[][]

);

CREATE TABLE

数组数据类型通过在数据类型后面加上方括号（[]）来命名。上述例子中创建一个名为sal_emp的表，它的列分别为：1、类型为text的列name；2、一维integer类型数据pay_by_quarter；3、二维text类型数组schedule。

同时，CREATE TABLE的语法也允许指定数组的确切大小，例如：

CREATE TABLE tictactoe (

    squares   integer[3][3]

);

CREATE TABLE

但是这种指定并不限制数组的大小和长度，并不影响后续运行时的行为。

或者写成：

pay_by_quarter  integer ARRAY[4],

或者：

pay_by_quarter  integer ARRAY,

数组的输入，一般将元素值用花括号包围并用逗号分隔。例如：

INSERT INTO sal_emp

    VALUES ('Bill',

    '{10000, 10000, 10000, 10000}',

    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT 0 1

INSERT INTO sal_emp

    VALUES ('Carol',

    ARRAY[20000, 25000, 25000, 25000],

    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

INSERT 0 1

数组的查询操作与表查询类似。例如：

SELECT * FROM sal_emp;

 name  |      pay_by_quarter       |                 schedule

-------+---------------------------+-------------------------------------------

 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}

 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}

(2 rows)

对所有员工第三季度工资查询：

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter

----------------

          10000

          25000

(2 rows)

数组的修改，可以使用UPDATE语句。例如：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'

    WHERE name = 'Carol';

UPDATE 1

数组的搜索：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

 name |      pay_by_quarter       |                 schedule

------+---------------------------+-------------------------------------------

 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}

(1 row)

1. 1. 1. 复合类型

复合类型用来表示一行或一个记录的结构。

复合类型的定义，例如：

CREATE TYPE complex AS (

    r       double precision,

    i       double precision

);

CREATE TYPE

CREATE TYPE inventory_item AS (

    name            text,

    supplier_id     integer,

    price           numeric

);

CREATE TYPE

复合类型定义后可以用来创建表或函数，例如：

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeri

AS 'SELECT $1.price * $2' LANGUAGE SQL;

CREATE FUNCTION

访问符合类型语法：

SELECT (my_func(...)).field FROM ...

1. 1. 1. 范围类型

GBase 8c带有下列内建范围类型：

1. int4range — integer的范围
2. int8range — bigint的范围
3. numrange — numeric的范围
4. tsrange — 不带时区的 timestamp的范围
5. tstzrange — 带时区的 timestamp的范围
6. daterange — date的范围

用户也可以利用CREATE TYPE创建自己的范围类型。

举例：

CREATE TABLE reservation (room int, during tsrange);

INSERT INTO reservation VALUES

    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含

SELECT int4range(10, 20) @> 3;

-- 重叠

SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 抽取上界

SELECT upper(int8range(15, 25));

-- 计算交集

SELECT int4range(10, 20) * int4range(15, 25);

-- 范围为空吗？

SELECT isempty(numrange(1, 5));

1. 1. 1. 对象标识符类型

对象标识符（OID）被用来在内部作为多个系统表的主键。OID不会被添加到用户创建的表中，除非在创建表时指定了WITH OIDS或者启用了default_with_oids配置变量。OID类型有多个别名：regproc、regprocedure、regoper、regoperator、regclass、regtype、regrole、regnamespace、regconfig和regdictionary。

名字	引用	描述	值示例
oid	任意	数字形式的对象标识符	564182
regproc	pg_proc	函数名字	sum
regprocedure	pg_proc	带参数类型的函数	sum(int4)
regoper	pg_operator	操作符名字	+
regoperator	pg_operator	带参数类型的操作符	*(integer,integer) or -(NONE,integer)
regclass	pg_class	关系名字	pg_type
regtype	pg_type	数据类型名字	integer
regrole	pg_authid	角色名	smithee
regnamespace	pg_namespace	名字空间名称	pg_catalog
regconfig	pg_ts_config	文本搜索配置	english
regdictionary	pg_ts_dict	文本搜索字典	simple

oid默认为无符号的4字节整数，在数据库中无法保证唯一性（不同Coordinator和Datanode间OID值可能相同），不建议作为用户定义表的主键使用。

1. 1. 1. pg_lsn Type

pg_lsn数据类型可以存储 LSN（日志序列号）数据，LSN 是一个指向 WAL 中的位置的指针。LSN是一个64位的整数，在日志中被打印成两个以斜线分隔的最高8位的十六进制数，例如：16/B374D848。如果将两个LSN做减法操作，其结果为两个日志位置的字节数。

1. 1. 1. 伪类型

伪类型不能作为列的数据类型，可以用来定义参数或结果类型。

名字	描述
any	表示一个函数可以接受任意输入数据类型。
anyelement	表示一个函数可以接受任意数据类型。
anyarray	表示一个函数可以接受任意数组数据类型
anynonarray	表示一个函数可以接受任意非数组数据类型。
anyenum	表示一个函数可以接受任意枚举数据类型。
anyrange	表示一个函数可以接受任意范围数据类型。
cstring	表示一个函数接受或者返回一个非空结尾的C字符串。
internal	表示一个函数接受或返回一个服务器内部数据类型。
language_handler	一个被声明为返回language_handler的过程语言调用处理器。
fdw_handler	一个被声明为返回fdw_handler的外部数据包装器处理器。
index_am_handler	一个被声明为返回index_am_handler索引访问方法处理器。
tsm_handler	一个被声明为返回tsm_handler的表采样方法处理器。
record	标识一个接收或者返回一个未指定的行类型的函数。
trigger	一个被声明为返回trigger的触发器函数。
event_trigger	一个被声明为返回event_trigger的事件触发器函数。
pg_ddl_command	标识一种对事件触发器可用的 DDL 命令的表达。
void	表示一个函数不返回值。
unknown	标识尚未解析的类型，例如，未装饰的字符串文字。
opaque	一种已被废弃的类型名称，以前它用于实现大多数以上的目的。