Table of Contents
- openGauss学习笔记-08 openGauss 数据类型
- 8.1 数值类型
- 8.2 布尔类型
- 8.3 字符类型
- 8.4 二进制类型
- 8.5 日期/时间类型
- 8.6 几何类型
- 8.7 网络地址类型
- 8.8 位串类型
- 8.9 文本搜索类型
- 8.10 UUID数据类型
- 8.11 JSON/JSONB类型
- 8.12 HLL数据类型
- 8.13 范围类型
- 8.14 索引
- 8.15 对象标识符类型
- 8.16 伪类型
- 8.17 列存表支持的数据类型
- 8.18 XML类型
- 8.19 账本数据库使用的数据类型
openGauss学习笔记-08 openGauss 数据类型
8.1 数值类型
表1列出了所有的可用类型。
表 1 整数类型
名称 | 描述 | 存储空间 | 范围 |
---|---|---|---|
TINYINT | 微整数,别名为INT1。 | 1字节 | 0 ~ 255 |
SMALLINT | 小范围整数,别名为INT2。 | 2字节 | -32,768 ~ +32,767 |
INTEGER | 常用的整数,别名为INT4。 | 4字节 | -2,147,483,648 ~ +2,147,483,647 |
BINARY_INTEGER | 常用的整数INTEGER的别名。 | 4字节 | -2,147,483,648 ~ +2,147,483,647 |
BIGINT | 大范围的整数,别名为INT8。 | 8字节 | -9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807 |
int16 | 十六字节的大范围整数,目前不支持用户用于建表等使用。 | 16字节 | -170,141,183,460,469,231,731,687,303,715,884,105,728 ~ +170,141,183,460,469,231,731,687,303,715,884,105,727 |
表 2 任意精度型
名称 | 描述 | 存储空间 | 范围 |
---|---|---|---|
NUMERIC[(p[,s])],DECIMAL[(p[,s])] | 精度p取值范围为[1,1000],标度s取值范围为[0,p]。说明:p为总位数,s为小数位数。 | 用户声明精度。每四位(十进制位)占用两个字节,然后在整个数据上加上八个字节的额外开销。 | 未指定精度的情况下,小数点前最大131,072位,小数点后最大16,383位。 |
NUMBER[(p[,s])] | NUMERIC类型的别名。 | 用户声明精度。每四位(十进制位)占用两个字节,然后在整个数据上加上八个字节的额外开销。 | 未指定精度的情况下,小数点前最大131,072位,小数点后最大16,383位。 |
表 3 序列整型
名称 | 描述 | 存储空间 | 范围 |
---|---|---|---|
SMALLSERIAL | 二字节序列整型。 | 2字节 | -32,768 ~ +32,767 |
SERIAL | 四字节序列整型。 | 4字节 | -2,147,483,648 ~ +2,147,483,647 |
BIGSERIAL | 八字节序列整型。 | 8字节 | -9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807 |
LARGESERIAL | 十六字节序列整型。 | 16字节 | -170,141,183,460,469,231,731,687,303,715,884,105,728 ~ +170,141,183,460,469,231,731,687,303,715,884,105,727 |
表 4 浮点类型
名称 | 描述 | 存储空间 | 范围 |
---|---|---|---|
REAL,FLOAT4 | 单精度浮点数,不精准。 | 4字节 | -3.402E+38~3.402E+38,6位十进制数字精度。 |
DOUBLE PRECISION,FLOAT8 | 双精度浮点数,不精准。 | 8字节 | -1.79E+308~1.79E+308,15位十进制数字精度。 |
FLOAT[§] | 浮点数,不精准。精度p取值范围为[1,53]。说明:p为精度,表示总位数。 | 4字节或8字节 | 根据精度p不同选择REAL或DOUBLE PRECISION作为内部表示。如不指定精度,内部用DOUBLE PRECISION表示。 |
BINARY_DOUBLE | 是DOUBLE PRECISION的别名。 | 8字节 | -1.79E+308~1.79E+308,15位十进制数字精度。 |
DEC[(p[,s])] | 精度p取值范围为[1,1000],标度s取值范围为[0,p]。说明:p为总位数,s为小数位位数。 | 用户声明精度。每四位(十进制位)占用两个字节,然后在整个数据上加上八个字节的额外开销。 | 未指定精度的情况下,小数点前最大131,072位,小数点后最大16,383位。 |
INTEGER[(p[,s])] | 精度p取值范围为[1,1000],标度s取值范围为[0,p]。 | 用户声明精度。每四位(十进制位)占用两个字节,然后在整个数据上加上八个字节的额外开销。 | - |
8.2 布尔类型
表 5 布尔类型
名称 | 描述 | 存储空间 | 取值 |
---|---|---|---|
BOOLEAN | 布尔类型 | 1字节。 | true:真false:假null:未知(unknown) |
8.3 字符类型
openGauss支持的字符类型请参见表6。
表 6 字符类型
名称 | 描述 | 存储空间 |
---|---|---|
CHAR(n)CHARACTER(n)NCHAR(n) | 定长字符串,不足补空格。n是指字节长度,如不带精度n,默认精度为1。 | 最大为10MB。 |
VARCHAR(n)CHARACTER VARYING(n) | 变长字符串。n是指字节长度。 | 最大为10MB。 |
VARCHAR2(n) | 变长字符串。是VARCHAR(n)类型的别名。n是指字节长度。 | 最大为10MB。 |
NVARCHAR2(n) | 变长字符串。n是指字符长度。 | 最大为10MB。 |
TEXT | 变长字符串。 | 最大为1GB-1,但还需要考虑到列描述头信息的大小, 以及列所在元组的大小限制(也小于1GB-1),因此TEXT类型最大大小可能小于1GB-1。 |
CLOB | 文本大对象。是TEXT类型的别名。 | 最大为1GB-1,但还需要考虑到列描述头信息的大小, 以及列所在元组的大小限制(也小于1GB-1),因此CLOB类型最大大小可能小于1GB-1。 |
说明:
- 除了每列的大小限制以外,每个元组的总大小也不可超过1GB-1字节,主要受列的控制头信息、元组控制头信息以及元组中是否存在NULL字段等影响。
- NCHAR为bpchar类型的别名,NCHAR(n)为b(n)类型bpchar(n)的别名。
在openGauss里另外还有两种定长字符类型。在表7里显示。name类型只用在内部系统表中,作为存储标识符,不建议普通用户使用。该类型长度当前定为64字节(63可用字符加结束符)。类型“char”只用了一个字节的存储空间。他在系统内部主要用于系统表,主要作为简单化的枚举类型使用。
表 7 特殊字符类型
名称 | 描述 | 存储空间 |
---|---|---|
name | 用于对象名的内部类型。 | 64字节。 |
“char” | 单字节内部类型。 | 1字节。 |
8.4 二进制类型
openGauss支持的二进制类型请参见表8。
表 8 二进制类型
名称 | 描述 | 存储空间 |
---|---|---|
BLOB | 二进制大对象说明:列存不支持BLOB类型 | 最大为1GB-8203字节(即1073733621字节)。 |
RAW | 变长的十六进制类型说明:列存不支持RAW类型 | 4字节加上实际的十六进制字符串。最大为1GB-8203字节(即1073733621字节)。 |
BYTEA | 变长的二进制字符串 | 4字节加上实际的二进制字符串。最大为1GB-8203字节(即1073733621字节)。 |
BYTEAWITHOUTORDERWITHEQUALCOL | 变长的二进制字符串(密态特性新增的类型,如果加密列的加密类型指定为确定性加密,则该列的实际类型为BYTEAWITHOUTORDERWITHEQUALCOL),元命令打印加密表将显示原始数据类型 | 4字节加上实际的二进制字符串。最大为1GB减去53字节(即1073741771字节)。 |
BYTEAWITHOUTORDERCOL | 变长的二进制字符串(密态特性新增的类型,如果加密列的加密类型指定为随机加密,则该列的实际类型为BYTEAWITHOUTORDERCOL),元命令打印加密表将显示原始数据类型 | 4字节加上实际的二进制字符串。最大为1GB减去53字节(即1073741771字节)。 |
_BYTEAWITHOUTORDERWITHEQUALCOL | 变长的二进制字符串,密态特性新增的类型 | 4字节加上实际的二进制字符串。最大为1GB减去53字节(即1073741771字节)。 |
_BYTEAWITHOUTORDERCOL | 变长的二进制字符串,密态特性新增的类型 | 4字节加上实际的二进制字符串。最大为1GB减去53字节(即1073741771字节)。 |
说明:
- 除了每列的大小限制以外,每个元组的总大小也不可超过1GB-8203字节(即1073733621字节)。
- 不支持直接使用BYTEAWITHOUTORDERWITHEQUALCOL和BYTEAWITHOUTORDERCOL,_BYTEAWITHOUTORDERWITHEQUALCOL,_BYTEAWITHOUTORDERCOL类型创建表。
8.5 日期/时间类型
openGauss支持的日期/时间类型请参见表9。
说明:
如果其他的数据库时间格式和openGauss的时间格式不一致,可通过修改配置参数DateStyle的值来保持一致。
表 9 日期/时间类型
名称 | 描述 | 存储空间 |
---|---|---|
DATE | 日期和时间。 | 4字节(实际存储空间大小为8字节) |
TIME [§] [WITHOUT TIME ZONE] | 只用于一日内时间。p表示小数点后的精度,取值范围为0~6。 | 8字节 |
TIME [§] [WITH TIME ZONE] | 只用于一日内时间,带时区。p表示小数点后的精度,取值范围为0~6。 | 12字节 |
TIMESTAMP[§] [WITHOUT TIME ZONE] | 日期和时间。p表示小数点后的精度,取值范围为0~6。 | 8字节 |
TIMESTAMP[§][WITH TIME ZONE] | 日期和时间,带时区。TIMESTAMP的别名为TIMESTAMPTZ。p表示小数点后的精度,取值范围为0~6。 | 8字节 |
SMALLDATETIME | 日期和时间,不带时区。精确到分钟,秒位大于等于30秒进一位。 | 8字节 |
INTERVAL DAY (l) TO SECOND § | 时间间隔,X天X小时X分X秒。l:天数的精度,取值范围为06。兼容性考虑,目前未实现具体功能。p:秒数的精度,取值范围为06。小数末尾的零不显示。 | 16字节 |
INTERVAL [FIELDS] [ § ] | 时间间隔。fields:可以是YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,DAY TO HOUR,DAY TO MINUTE,DAY TO SECOND,HOUR TO MINUTE,HOUR TO SECOND,MINUTE TO SECOND。p:秒数的精度,取值范围为0~6,且fields为SECOND,DAY TO SECOND,HOUR TO SECOND或MINUTE TO SECOND时,参数p才有效。小数末尾的零不显示。 | 12字节 |
reltime | 相对时间间隔。格式为:X years X mons X days XX:XX:XX。采用儒略历计时,规定一年为365.25天,一个月为30天,计算输入值对应的相对时间间隔,输出采用POSTGRES格式。 | 4字节 |
abstime | 日期和时间。格式为:YYYY-MM-DD hh:mm:ss+timezone取值范围为1901-12-13 20:45:53 GMT~2038-01-18 23:59:59 GMT,精度为秒。 | 4字节 |
8.6 几何类型
openGauss支持的几何类型请参见表10。最基本的类型:点,是其它类型的基础。
表 10 几何类型
名称 | 存储空间 | 说明 | 表现形式 |
---|---|---|---|
point | 16字节 | 平面中的点 | (x,y) |
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 字节 | 圆 | (圆心和半径) |
openGauss提供了一系列的函数和操作符用来进行各种几何计算,如拉伸、转换、旋转、计算相交等。
-
点
点是几何类型的基本二维构造单位。用下面语法描述point的数值:
( x , y )
x , y
x和y是用浮点数表示的点的坐标。
点输出使用第一种语法。
-
线段
线段(lseg)是用一对点来代表的。用下面的语法描述lseg的数值:
[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
(x1,y1)和(x2,y2)表示线段的端点。
线段输出使用第一种语法。
-
矩形
矩形是用一对对角点来表示的。用下面的语法描述box的值:
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
(x1,y1)和(x2,y2)表示矩形的一对对角点。
矩形的输出使用第二种语法。
任何两个对角都可以出现在输入中,但按照那样的顺序,右上角和左下角的值会被重新排序以存储。
-
路径
路径由一系列连接的点组成。路径可能是开放的,也就是认为列表中第一个点和最后一个点没有连接,也可能是闭合的,这时认为第一个和最后一个点连接起来。
用下面的语法描述path的数值:
[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( ( x1 , y1 ) , ... , ( xn , yn ) )
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
点表示组成路径的线段的端点。方括弧([])表明一个开放的路径,圆括弧(())表明一个闭合的路径。当最外层的括号被省略,如在第三至第五语法,会假定一个封闭的路径。
路径的输出使用第一种或第二种语法输出。
-
多边形
多边形由一系列点代表(多边形的顶点)。多边形可以认为与闭合路径一样,但是存储方式不一样而且有自己的一套支持函数。
用下面的语法描述polygon的数值:
( ( x1 , y1 ) , ... , ( xn , yn ) )
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
点表示多边形的端点。
多边形输出使用第一种语法。
-
圆
圆由一个圆心和半径标识。用下面的语法描述circle的数值:
( ( x , y ) , r )
( x , y ) , r
x , y , r
(x,y)表示圆心,r表示半径。
圆的输出用第一种格式。
8.7 网络地址类型
openGauss提供用于存储IPv4、IPv6、MAC地址的数据类型。
用这些数据类型存储网络地址比用纯文本类型好,因为这些类型提供输入错误检查和特殊的操作和功能。
表 11 网络地址类型
名称 | 存储空间 | 描述 |
---|---|---|
cidr | 7或19字节 | IPv4或IPv6网络 |
inet | 7或19字节 | IPv4或IPv6主机和网络 |
macaddr | 6字节 | MAC地址 |
在对inet或cidr数据类型进行排序的时候,IPv4地址总是排在IPv6地址前面,包括那些封装或者是映射在IPv6地址里的IPv4地址,比如::10.2.3.4或::ffff:10.4.3.2。
-
cidr
cidr(无类别域间路由,Classless Inter-Domain Routing)类型,保存一个IPv4或IPv6网络地址。声明网络格式为address/y,address表示IPv4或者IPv6地址,y表示子网掩码的二进制位数。如果省略y,则掩码部分使用已有类别的网络编号系统进行计算,但要求输入的数据已经包括了确定掩码所需的所有字节。
表 12 cidr类型输入举例
cidr输入 | cidr输出 | abbrev(cidr) |
---|---|---|
192.168.100.128/25 | 192.168.100.128/25 | 192.168.100.128/25 |
192.168/24 | 192.168.0.0/24 | 192.168.0/24 |
192.168/25 | 192.168.0.0/25 | 192.168.0.0/25 |
192.168.1 | 192.168.1.0/24 | 192.168.1/24 |
192.168 | 192.168.0.0/24 | 192.168.0/24 |
10.1.2 | 10.1.2.0/24 | 10.1.2/24 |
10.1 | 10.1.0.0/16 | 10.1/16 |
10 | 10.0.0.0/8 | 10/8 |
10.1.2.3/32 | 10.1.2.3/32 | 10.1.2.3/32 |
2001:4f8:3:ba::/64 | 2001:4f8:3:ba::/64 | 2001:4f8:3:ba::/64 |
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 | 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 | 2001:4f8:3:ba:2e0:81ff:fe22:d1f1 |
::ffff:1.2.3.0/120 | ::ffff:1.2.3.0/120 | ::ffff:1.2.3/120 |
::ffff:1.2.3.0/128 | ::ffff:1.2.3.0/128 | ::ffff:1.2.3.0/128 |
-
inet
inet类型在一个数据区域内保存主机的IPv4或IPv6地址,以及一个可选子网。主机地址中网络地址的位数表示子网(“子网掩码”)。如果子网掩码是32并且地址是IPv4,则这个值不表示任何子网,只表示一台主机。在IPv6里,地址长度是128位,因此128位表示唯一的主机地址。
该类型的输入格式是address/y,address表示IPv4或者IPv6地址,y是子网掩码的二进制位数。如果省略/y,则子网掩码对IPv4是32,对IPv6是128,所以该值表示只有一台主机。如果该值表示只有一台主机,/y将不会显示。
inet和cidr类型之间的基本区别是inet接受子网掩码,而cidr不接受。
-
macaddr
macaddr类型存储MAC地址,也就是以太网卡硬件地址(尽管MAC地址还用于其它用途)。可以接受下列格式:
'08:00:2b:01:02:03'
'08-00-2b-01-02-03'
'08002b:010203'
'08002b-010203'
'0800.2b01.0203'
'08002b010203'
这些示例都表示同一个地址。对于数据位a到f,大小写都行。输出时都是以第一种形式展示。
8.8 位串类型
位串就是一串1和0的字符串。它们可以用于存储位掩码。
openGauss支持两种位串类型:bit(n)和bit varying(n),这里的n是一个正整数。
bit类型的数据必须准确匹配长度n,如果存储短或者长的数据都会报错。bit varying类型的数据是最长为n的变长类型,超过n的类型会被拒绝。一个没有长度的bit等效于bit(1),没有长度的bit varying表示没有长度限制。
说明: 如果用户明确地把一个位串值转换成bit(n),则此位串右边的内容将被截断或者在右边补齐零,直到刚好n位,而不会抛出任何错误。 如果用户明确地把一个位串数值转换成bit varying(n),如果它超过了n位,则它的右边将被截断。
--创建表。
openGauss=# CREATE TABLE bit_type_t1
(
BT_COL1 INTEGER,
BT_COL2 BIT(3),
BT_COL3 BIT VARYING(5)
) ;
--插入数据。
openGauss=# INSERT INTO bit_type_t1 VALUES(1, B'101', B'00');
--插入数据的长度不符合类型的标准会报错。
openGauss=# INSERT INTO bit_type_t1 VALUES(2, B'10', B'101');
ERROR: bit string length 2 does not match type bit(3)
CONTEXT: referenced column: bt_col2
--将不符合类型长度的数据进行转换。
openGauss=# INSERT INTO bit_type_t1 VALUES(2, B'10'::bit(3), B'101');
--查看数据。
openGauss=# SELECT * FROM bit_type_t1;
bt_col1 | bt_col2 | bt_col3
---------+---------+---------
1 | 101 | 00
2 | 100 | 101
(2 rows)
--删除表。
openGauss=# DROP TABLE bit_type_t1;
8.9 文本搜索类型
openGauss提供了两种数据类型用于支持全文检索。tsvector类型表示为文本搜索优化的文件格式,tsquery类型表示文本查询。
-
tsvector
tsvector类型表示一个检索单元,通常是一个数据库表中一行的文本字段或者这些字段的组合,tsvector类型的值是一个标准词位的有序列表,标准词位就是把同一个词的变型体都标准化成相同的,在输入的同时会自动排序和消除重复。to_tsvector函数通常用于解析和标准化文档字符串。
tsvector的值是唯一分词的分类列表,把一句话的词格式化为不同的词条,在进行分词处理的时候tsvector会自动去掉分词中重复的词条,按照一定的顺序录入。如:
openGauss=# SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
tsvector
----------------------------------------------------
'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
(1 row)
从上面的例子可以看出,通过tsvector把一个字符串按照空格进行分词,分词的顺序是按照长短和字母排序的。但是如果词条中需要包含空格或标点符号,可以用引号标记:
openGauss=# SELECT $$the lexeme ' ' contains spaces$$::tsvector;
tsvector
-------------------------------------------
' ' 'contains' 'lexeme' 'spaces' 'the'
(1 row)
如果在词条中使用引号,可以使用双符号($)作为标记:
openGauss=# SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
tsvector
------------------------------------------------
'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'
(1 row)
词条位置常量也可以放到词汇中:
openGauss=# SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;
tsvector
-------------------------------------------------------------------------------
'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4
(1 row)
位置常量通常表示文档中源字的位置。位置信息可以用于进行排名。位置常量的范围是1到16383,最大值默认是16383。相同词的重复位会被忽略掉。
拥有位置的词汇甚至可以用一个权来标记,这个权可以是A、B、C或D。默认的是D,因此输出中不会出现:
openGauss=# SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;
tsvector
----------------------------
'a':1A 'cat':5 'fat':2B,4C
(1 row)
权可以用来反映文档结构,如:标记标题与主体文字的区别。全文检索排序函数可以为不同的权标记分配不同的优先级。
下面的示例是tsvector类型标准用法。如:
openGauss=# SELECT 'The Fat Rats'::tsvector;
tsvector
--------------------
'Fat' 'Rats' 'The'
(1 row)
但是对于英文全文检索应用来说,上面的单词会被认为非规范化的,所以需要通过to_tsvector函数对这些单词进行规范化处理:
openGauss=# SELECT to_tsvector('english', 'The Fat Rats');
to_tsvector
-----------------
'fat':2 'rat':3
(1 row)
-
tsquery
tsquery类型表示一个检索条件,存储用于检索的词汇,并且使用布尔操作符&(AND),|(OR)和!(NOT)来组合他们,括号用来强调操作符的分组。to_tsquery函数及plainto_tsquery函数会将单词转换为tsquery类型前进行规范化处理。
openGauss=# SELECT 'fat & rat'::tsquery;
tsquery
---------------
'fat' & 'rat'
(1 row)openGauss=# SELECT 'fat & (rat | cat)'::tsquery;
tsquery
---------------------------
'fat' & ( 'rat' | 'cat' )
(1 row)openGauss=# SELECT 'fat & rat & ! cat'::tsquery;
tsquery
------------------------
'fat' & 'rat' & !'cat'
(1 row)
在没有括号的情况下,!(非)结合的最紧密,而&(和)结合的比|(或)紧密。
tsquery中的词汇可以用一个或多个权字母来标记,这些权字母限制这次词汇只能与带有匹配权的tsvector词汇进行匹配。
openGauss=# SELECT 'fat:ab & cat'::tsquery;
tsquery
------------------
'fat':AB & 'cat'
(1 row)
同样,tsquery中的词汇可以用*标记来指定前缀匹配:
openGauss=# SELECT 'super:*'::tsquery;
tsquery
-----------
'super':*
(1 row)
这个查询可以匹配tsvector中以“super”开始的任意单词。
请注意,前缀首先被文本搜索分词器处理,这也就意味着下面的结果为真:
openGauss=# SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' ) AS RESULT;
result
----------
t
(1 row)
因为postgres经过处理后得到postgr:
openGauss=# SELECT to_tsquery('postgres:*');
to_tsquery
------------
'postgr':*
(1 row)
这样就匹配postgraduate了。
'Fat:ab & Cats’规范化转为tsquery类型结果如下:
openGauss=# SELECT to_tsquery('Fat:ab & Cats');
to_tsquery
------------------
'fat':AB & 'cat'
(1 row)
8.10 UUID数据类型
UUID数据类型用来存储RFC 4122,ISO/IEF 9834-8:2005以及相关标准定义的通用唯一标识符(UUID)。这个标识符是一个由算法产生的128位标识符,确保它不可能使用相同算法在已知的模块中产生的相同标识符。
UUID是一个小写十六进制数字的序列,由分字符分成几组,一组8位数字+三组4位数字+一组12位数字,总共32个数字代表128位,标准的UUID示例如下:
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
openGauss同样支持以其他方式输入:大写字母和数字、由花括号包围的标准格式、省略部分或所有连字符、在任意一组四位数字之后加一个连字符。示例:
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
一般是以标准格式输出。
8.11 JSON/JSONB类型
JSON(JavaScript Object Notation)数据,可以是单独的一个标量,也可以是一个数组,也可以是一个键值对象,其中数组和对象可以统称容器(container):
- 标量(scalar):单一的数字、bool、string、null都可以叫做标量。
- 数组(array):[]结构,里面存放的元素可以是任意类型的JSON,并且不要求数组内所有元素都是同一类型。
- 对象(object):{}结构,存储key:value的键值对,其键只能是用“”包裹起来的字符串,值可以是任意类型的JSON,对于重复的键,按最后一个键值对为准。
openGauss内存在两种数据类型JSON和JSONB,可以用来存储JSON数据。其中JSON是对输入的字符串的完整拷贝,使用时再去解析,所以它会保留输入的空格、重复键以及顺序等;JSONB解析输入后保存的二进制,它在解析时会删除语义无关的细节和重复的键,对键值也会进行排序,使用时不用再次解析。
因此可以发现,两者其实都是JSON,它们接受相同的字符串作为输入。它们实际的主要差别是效率。JSON数据类型存储输入文本的精确拷贝,处理函数必须在每个执行上重新解析; 而JSONB数据以分解的二进制格式存储, 这使得它由于添加了转换机制而在输入上稍微慢些,但是在处理上明显更快, 因为不需要重新解析。同时由于JSONB类型存在解析后的格式归一化等操作,同等的语义下只会有一种格式,因此可以更好更强大的支持很多其他额外的操作,比如按照一定的规则进行大小比较等。JSONB也支持索引,这也是一个明显的优势。
-
输入格式
输入必须是一个符合JSON数据格式的字符串,此字符串用单引号’'声明。
null (null-json):仅null,全小写。
select 'null'::json; -- suc
select 'NULL'::jsonb; -- err
数字 (num-json):正负整数、小数、0,支持科学计数法。
select '1'::json;
select '-1.5'::json;
select '-1.5e-5'::jsonb, '-1.5e+2'::jsonb;
select '001'::json, '+15'::json, 'NaN'::json;
-- 不支持多余的前导0,正数的+号,以及NaN和infinity。
布尔(bool-json):仅true、false,全小写。
select 'true'::json;
select 'false'::jsonb;
字符串(str-json):必须是加双引号的字符串。
select '"a"'::json;
select '"abc"'::jsonb;
数组(array-json):使用中括号[]包裹,满足数组书写条件。数组内元素类型可以是任意合法的JSON,且不要求类型一致。
select '[1, 2, "foo", null]'::json;
select '[]'::json;
select '[1, 2, "foo", null, [[]], {}]'::jsonb;
对象(object-json):使用大括号{}包裹,键必须是满足JSON字符串规则的字符串,值可以是任意合法的JSON。
select '{}'::json;
select '{"a": 1, "b": {"a": 2, "b": null}}'::json;
select '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::jsonb;
注意:
- 区分 ‘null’::json 和 null::json 是两个不同的概念,类似于字符串 str= 和 str=null。
- 对于数字,当使用科学计数法的时候,jsonb类型会将其展开,而json会精准拷贝输入。
-
JSONB高级特性
- 注意事项
- 不支持列存。
- 不支持作为分区键。
- 不支持外表、mot。
JSON和JSONB的主要差异在于存储方式上的不同,JSONB存储的是解析后的二进制,能够体现JSON的层次结构,更方便直接访问等,因此JSONB会有很多JSON所不具有的高级特性。
-
格式归一化
-
对于输入的object-json字符串,解析成jsonb二进制后,会天然的丢弃语义上无关紧要的细节,比如空格:
openGauss=# select ' [1, " a ", {"a" :1 }] '::jsonb;
jsonb
----------------------
[1, " a ", {"a": 1}]
(1 row)
-
对于object-json,会删除重复的键值,只保留最后一个出现的,如:
openGauss=# select '{"a" : 1, "a" : 2}'::jsonb;
jsonb
----------
{"a": 2}
(1 row)
-
对于object-json,键值会重新进行排序,排序规则:长度长的在后、长度相等则ascii码大的在后,如:
openGauss=# select '{"aa" : 1, "b" : 2, "a" : 3}'::jsonb;
jsonb
---------------------------
{"a": 3, "b": 2, "aa": 1}
(1 row)
-
- 注意事项
-
大小比较
由于经过了格式归一化,保证了同一种语义下的jsonb只会有一种存在形式,因此按照制定的规则,可以比较大小。
-
首先比较类型:object-jsonb > array-jsonb > bool-jsonb > num-jsonb > str-jsonb > null-jsonb
-
同类型则比较内容:
- str-json类型:依据text比较的方法,使用数据库默认排序规则进行比较,返回值正数代表大于,负数代表小于,0表示相等。
- num-json类型:数值比较
- bool-json类型:true > false
- array-jsonb类型:长度长的 > 长度短的,长度相等则依次比较每个元素。
- object-jsonb类型:长度长的 > 长度短的,长度相等则依次比较每个键值对,先比较键,在比较值。
注意:
object-jsonb类型内比较时,比较时使用的是格式整理后的最终结果进行比较,因此相对于我们直接的输入未必会很直观。
-
-
创建索引、主外键
-
BTREE索引
jsonb类型支持创建btree索引,支持创建主键、外键。
-
GIN索引
GIN索引可以用来有效地搜索出现在大量jsonb文档(datums) 中的键或者键/值对。提供了两个GIN操作符类(jsonb_ops、jsonb_hash_ops),提供了不同的性能和灵活性取舍。缺省的GIN操作符类支持使用@>、、 '"foo"'::jsonb;
-- 左侧数组包含了右侧字符串。
SELECT '[1, "aa", 3]'::jsonb ? 'aa';
-- 左侧数组包含了右侧的数组所有元素,顺序、重复不重要。
SELECT '[1, 2, 3]'::jsonb @> '[1, 3, 1]'::jsonb;
-- 左侧object-json包含了右侧object-json的所有键值对。
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb":true}'::jsonb @> '{"version":9.4}'::jsonb;
-- 左侧数组并没有包含右侧的数组所有元素,因为左侧数组的三个元素为1、2、[1,3],右侧的为1、3。
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; --false
-- 同上,没有存在包含关系,返回值为false。
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- false
8.12 HLL数据类型
HLL(HyperLoglog)是统计数据集中唯一值个数的高效近似算法。它有着计算速度快、节省空间的特点,不需要直接存储集合本身,而是存储一种名为HLL的数据结构。每当有新数据加入进行统计时,只需要把数据经过哈希计算并插入到HLL中,最后根据HLL就可以得到结果。
HLL与其他算法的比较请参见表13。
表 13 HLL与其他算法比较
项目 Sort算法 Hash算法 HLL 时间复杂度 O(nlogn) O(n) O(n) 空间复杂度 O(n) O(n) log(logn) 误差率 0 0 ≈0.8% 所需存储空间 原始数据大小 原始数据大小 默认规格下最大16KB HLL在计算速度和所占存储空间上都占优势。在时间复杂度上,Sort算法需要排序至少O(nlogn)的时间,虽说Hash算法和HLL一样扫描一次全表O(n)的时间就可以得出结果,但是存储空间上,Sort算法和Hash算法都需要先把原始数据存起来再进行统计,会导致存储空间消耗巨大,而对HLL来说不需要存原始数据,只需要维护HLL数据结构,故占用空间有很大的压缩,默认规格下HLL数据结构的最大空间约为16KB。
须知:
- 当前默认规格下可计算最大distinct值的数量约为1.1e+15个,误差率为0.8%。用户应注意如果计算结果超过当前规格下distinct最大值会导致计算结果误差率变大,或导致计算结果失败并报错。
- 用户在首次使用该特性时,应该对业务的distinct value做评估,选取适当的配置参数并做验证,以确保精度符合要求:
- 当前默认参数下,可以计算的distinct值为1.1e+15,如果计算得到的distinct值为NaN,需要调整log2m,或者采用其他算法计算distinct值。
- 虽然hash算法存在极低的hash collision概率,但是建议用户在首次使用时,选取2-3个hash seed验证,如果得到的distinct value相差不大,则可以从该组seed中任选一个作为hash seed。
HLL中主要的数据结构,请参见表14。
表 14 HyperLogLog中主要数据结构
数据类型 功能描述 hll hll头部为27字节长度字段,默认规格下数据段长度0~16KB,可直接计算得到distinct值。 创建HLL数据类型时,可以支持04个参数入参,具体的参数含义与参数规格同函数hll_empty一致。第一个参数为log2m,表示分桶数的对数值,取值范围1016;第二个参数为log2explicit,表示Explicit模式的阈值大小,取值范围012;第三个参数为log2sparse,表示Sparse模式的阈值大小,取值范围014;第四个参数为duplicatecheck,表示是否启用duplicatecheck,取值范围为0~1。当入参输入值为-1时,会采用默认值设定HLL的参数。可以通过d或d+查看HLL类型的参数。
说明:
创建HLL数据类型时,根据入参的行为不同,结果不同:- 创建HLL类型时对应入参不输入或输入-1,采用默认值设定对应的HLL参数。
- 输入合法范围的入参,对应HLL参数采用输入值。
- 输入不合法范围的入参,创建HLL类型报错。
-- 创建hll类型的表,不指定入参
openGauss=# create table t1 (id integer, set hll);
openGauss=# d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
set | hll |-- 创建hll类型的表,指定前两个入参,后两个采用默认值
openGauss=# create table t2 (id integer, set hll(12,4));
openGauss=# d t2
Table "public.t2"
Column | Type | Modifiers
--------+----------------+-----------
id | integer |
set | hll(12,4,12,0) |--创建hll类型的表,指定第三个入参,其余采用默认值
openGauss=# create table t3(id int, set hll(-1,-1,8,-1));
openGauss=# d t3
Table "public.t3"
Column | Type | Modifiers
--------+----------------+-----------
id | integer |
set | hll(14,10,8,0) |--创建hll类型的表,指定入参不合法报错
openGauss=# create table t4(id int, set hll(5,-1));
ERROR: log2m = 5 is out of range, it should be in range 10 to 16, or set -1 as default
说明:
对含有HLL类型的表插入HLL对象时,HLL类型的设定参数须同插入对象的设定参数一致,否则报错。-- 创建带有hll类型的表
openGauss=# create table t1(id integer, set hll(14));-- 向表中插入hll对象,参数一致,成功
openGauss=# insert into t1 values (1, hll_empty(14,-1));-- 向表中插入hll对象,参数不一致,失败
openGauss=# insert into t1(id, set) values (1, hll_empty(14,5));
ERROR: log2explicit does not match: source is 5 and dest is 10
HLL的应用场景。
-
场景1:“Hello World”
通过下面的示例说明如何使用hll数据类型:
-- 创建带有hll类型的表
openGauss=# create table helloworld (id integer, set hll);-- 向表中插入空的hll
openGauss=# insert into helloworld(id, set) values (1, hll_empty());-- 把整数经过哈希计算加入到hll中
openGauss=# update helloworld set set = hll_add(set, hll_hash_integer(12345)) where id = 1;-- 把字符串经过哈希计算加入到hll中
openGauss=# update helloworld set set = hll_add(set, hll_hash_text('hello world')) where id = 1;-- 得到hll中的distinct值
openGauss=# select hll_cardinality(set) from helloworld where id = 1;
hll_cardinality
-----------------
2
(1 row)-- 删除表
openGauss=# drop table helloworld;
-
场景2:“网站访客数量统计”
通过下面的示例说明hll如何统计在一段时间内访问网站的不同用户数量:
-- 创建原始数据表,表示某个用户在某个时间访问过网站。
openGauss=# create table facts (
date date,
user_id integer
);-- 构造数据,表示一天中有哪些用户访问过网站。
openGauss=# insert into facts values ('2019-02-20', generate_series(1,100));
openGauss=# insert into facts values ('2019-02-21', generate_series(1,200));
openGauss=# insert into facts values ('2019-02-22', generate_series(1,300));
openGauss=# insert into facts values ('2019-02-23', generate_series(1,400));
openGauss=# insert into facts values ('2019-02-24', generate_series(1,500));
openGauss=# insert into facts values ('2019-02-25', generate_series(1,600));
openGauss=# insert into facts values ('2019-02-26', generate_series(1,700));
openGauss=# insert into facts values ('2019-02-27', generate_series(1,800));-- 创建表并指定列为hll。
openGauss=# create table daily_uniques (
date date UNIQUE,
users hll
);-- 根据日期把数据分组,并把数据插入到hll中。
openGauss=# insert into daily_uniques(date, users)
select date, hll_add_agg(hll_hash_integer(user_id))
from facts
group by 1;-- 计算每一天访问网站不同用户数量
openGauss=# select date, hll_cardinality(users) from daily_uniques order by date;
date | hll_cardinality
------------+------------------
2019-02-20 | 100
2019-02-21 | 200.217913059312
2019-02-22 | 301.76494508014
2019-02-23 | 400.862858326446
2019-02-24 | 502.626933349694
2019-02-25 | 601.922606454213
2019-02-26 | 696.602316769498
2019-02-27 | 798.111731634412
(8 rows)-- 计算在2019.02.20到2019.02.26一周中有多少不同用户访问过网站
openGauss=# select hll_cardinality(hll_union_agg(users)) from daily_uniques where date >= '2019-02-20'::date and date = 2.6.23) is required for XML support"的报错。 - 在执行编译之前,需要三方库二进制文件中dependency操作系统环境/libobs/comm/lib加入到系统环境变量LD_LIBRARY_PATH中,否则会报错"libiconv.so不存在"。
8.19 账本数据库使用的数据类型
账本数据库使用HASH16数据类型来存储行级hash摘要或表级hash摘要,使用HASH32数据类型来存储全局hash摘要或者历史表校验hash。
表 18 账本数据库HASH类型
名称 描述 存储空间 范围 HASH16 以无符号64位整数存储。 8字节 0 ~ +18446744073709551615 HASH32 以包含16个的无符号整型元素数的组存储。 16字节 16个元素的无符号整型数组能够包含的取值范围 HASH16数据类型用来在账本数据库中存储行级或表级hash摘要,在获得长度为16个字符串的十六进制字符串的hash序列后,系统将调用hash16in函数将该序列转换为一个无符号64位整数存储进HASH16类型变量中。示例如下:
十六进制字符串:e697da2eaa3a775b 对应的无符号64位整数:16615989244166043483
十六进制字符串:ffffffffffffffff 对应的无符号64位整数:18446744073709551615
HASH32数据类型用来在账本数据库中存储全局hash摘要或者历史表校验hash,在获得长度为32个字符串的十六进制字符串的hash序列后,系统将调用hash32in函数将该序列转换到一个包含16个无符号整型元素的数组中。示例如下:
十六进制字符串:685847ed1fe38e18f6b0e2b18c00edee
对应的HASH32数组:[104,88,71,237,31,227,142,24,246,176,226,177,140,0,237,238]
👍 点赞,你的认可是我创作的动力!
⭐️ 收藏,你的青睐是我努力的方向!
✏️ 评论,你的意见是我进步的财富!
-