中国航空集团建设开发有限公司网站,黄骅市企业名录,wordpress调用自定义类型文章,婚庆公司收费标准文章目录 1.openGauss SQL 语法2. 数据类型2.1数值类型2.2 布尔类型2.3 字符类型2.4 二进制类型2.5日期/时间类型2.6 几何类型2.7 网络地址类型2.8 位串类型2.9 文本搜索类型2.10 UUID数据类型2.11 JSON/JSONB类型2.11 HLL数据类型2.12 范围类型2.13 索引2.14 对象标识符类型2.… 文章目录 1.openGauss SQL 语法2. 数据类型2.1数值类型2.2 布尔类型2.3 字符类型2.4 二进制类型2.5日期/时间类型2.6 几何类型2.7 网络地址类型2.8 位串类型2.9 文本搜索类型2.10 UUID数据类型2.11 JSON/JSONB类型2.11 HLL数据类型2.12 范围类型2.13 索引2.14 对象标识符类型2.15 伪类型2.16 列存表支持的数据类型2.17 XML类型2.18 账本数据库使用的数据类型 1.openGauss SQL 语法
为了方便大家快速学习我整理了一下和MySQL的语法对比。 好的以下是 openGauss 和 MySQL 常用 SQL 命令的对比表格
openGauss 命令MySQL 命令简要介绍ABORTROLLBACK取消当前事务。ALTER TABLEALTER TABLE修改表的结构包括增加、删除、修改列等。CREATE DATABASECREATE DATABASE创建新的数据库。CREATE TEXT SEARCH CONFIGURATION无对应命令创建全文搜索配置。DROP OPERATOR无对应命令删除自定义操作符。MERGE无对应命令将两个或多个表中的数据合并到一个表中。ALTER APP WORKLOAD GROUP无对应命令修改应用工作负载组的属性。ALTER TABLE PARTITION无对应命令修改分区表的结构。CREATE DIRECTORY无对应命令创建新的目录。CREATE TEXT SEARCH DICTIONARY无对应命令创建全文搜索词典。DROP OWNED无对应命令删除指定所有者拥有的所有对象。MOVE无对应命令将表移到新的表空间。ALTER APP WORKLOAD GROUP MAPPING无对应命令修改应用工作负载组映射。ALTER AUDIT POLICY无对应命令修改审计策略。ALTER TEXT SEARCH CONFIGURATION无对应命令修改全文搜索配置。CREATE FOREIGN TABLECREATE TABLE创建外部表。CREATE TYPECREATE TYPE创建新的用户自定义类型。DROP PACKAGE无对应命令删除包。PREPAREPREPARE准备要执行的 SQL 语句。ALTER DATA SOURCE无对应命令修改数据源。CREATE FUNCTIONCREATE FUNCTION创建新的函数。CREATE USERCREATE USER创建新的用户。DROP PROCEDUREDROP PROCEDURE删除存储过程。PUBLISH SNAPSHOT无对应命令发布快照。PURGE SNAPSHOT无对应命令清除快照。ALTER DEFAULT PRIVILEGES无对应命令修改默认权限。ALTER DIRECTORY无对应命令修改目录的属性。ALTER Extension无对应命令修改扩展的属性。CREATE INDEXCREATE INDEX创建新的索引。CREATE WEAK PASSWORD DICTIONARY无对应命令创建弱密码词典。DROP RESOURCE POOL无对应命令删除资源池。REFRESH MATERIALIZED VIEW无对应命令刷新材料化视图。REINDEXREPAIR TABLE重建索引。ALTER FOREIGN TABLE FOR HDFS无对应命令修改外部表的属性。ANALYSEANALYZE TABLE分析表格。CREATE NODE无对应命令创建节点。DELETEDELETE从表中删除数据。DROP SERVER无对应命令删除服务器。ROLLBACKROLLBACK撤销一个事务。ROLLBACK PREPARED无对应命令撤销预备事务。SAMPLE SNAPSHOT无对应命令创建样本快照。SAVEPOINTSAVEPOINT在当前事务中创建保存点。SELECTSELECT从表中选择数据。SELECT INTOCREATE TABLE SELECT将查询结果插入到新表中。SETSET设置参数。SET CONSTRAINTSSET FOREIGN_KEY_CHECKS启用或禁用约束。SET ROLESET ROLE设置当前用户的角色。SET SESSION AUTHORIZATION无对应命令设置会话授权。SET TRANSACTIONSET TRANSACTION设置事务的属性。TRUNCATETRUNCATE TABLE删除表中的所有数据。UPDATEUPDATE更新表中的数据。VACUUMOPTIMIZE TABLE优化表格回收空间。ALTER DATABASEALTER DATABASE修改数据库的属性。ALTER OPERATOR无对应命令修改自定义操作符。CREATE AGGREGATECREATE AGGREGATE创建新的聚合函数。CREATE TABLECREATE TABLE创建新的表格。DROP AGGREGATEDROP AGGREGATE删除聚合函数。DROP DATABASEDROP DATABASE删除数据库。DROP TABLEDROP TABLE删除表格。GRANTGRANT授权。INSERTINSERT向表中插入数据。LOCK TABLELOCK TABLES锁定表格。RESETRESET重置参数为默认值。RESET ROLE无对应命令重置当前用户的角色。REVOKEREVOKE取消授权。UNLOCK TABLEUNLOCK TABLES解锁表格。COMMENT ONCOMMENT在对象上添加注释。CREATE SCHEMACREATE SCHEMA创建新的模式。DROP FUNCTIONDROP FUNCTION删除函数。DROP ROLEDROP USER删除用户。SET CONFIGURATION无对应命令设置配置项的值。SHOWSHOW显示相关信息。START TRANSACTIONSTART TRANSACTION开始一个新事务。COMMITCOMMIT提交一个事务。 ABORT取消当前事务。ALTER TABLE修改表的结构包括增加、删除、修改列等。CREATE DATABASE创建新的数据库。CREATE TEXT SEARCH CONFIGURATION创建全文搜索配置。DROP OPERATOR删除自定义操作符。MERGE将两个或多个表中的数据合并到一个表中。ALTER APP WORKLOAD GROUP修改应用工作负载组的属性。ALTER TABLE PARTITION修改分区表的结构。CREATE DIRECTORY创建新的目录。CREATE TEXT SEARCH DICTIONARY创建全文搜索词典。DROP OWNED删除指定所有者拥有的所有对象。MOVE将表移到新的表空间。ALTER APP WORKLOAD GROUP MAPPING修改应用工作负载组映射。ALTER AUDIT POLICY修改审计策略。ALTER TEXT SEARCH CONFIGURATION修改全文搜索配置。CREATE FOREIGN TABLE创建外部表。CREATE TYPE创建新的用户自定义类型。DROP PACKAGE删除包。PREPARE准备要执行的 SQL 语句。ALTER DATA SOURCE修改数据源。CREATE FUNCTION创建新的函数。CREATE USER创建新的用户。DROP PROCEDURE删除存储过程。PUBLISH SNAPSHOT发布快照。PURGE SNAPSHOT清除快照。ALTER DEFAULT PRIVILEGES修改默认权限。ALTER DIRECTORY修改目录的属性。ALTER Extension修改扩展的属性。CREATE INDEX创建新的索引。CREATE WEAK PASSWORD DICTIONARY创建弱密码词典。DROP RESOURCE POOL删除资源池。REASSIGN OWNED重新分配对象的所有权。REFRESH MATERIALIZED VIEW刷新材料化视图。REINDEX重建索引。ALTER FOREIGN TABLE FOR HDFS修改外部表的属性。ANALYSE分析表格。CREATE NODE创建节点。DELETE从表中删除数据。DROP SERVER删除服务器。ROLLBACK撤销一个事务。ROLLBACK PREPARED撤销预备事务。SAMPLE SNAPSHOT创建样本快照。SAVEPOINT在当前事务中创建保存点。SELECT从表中选择数据。SELECT INTO将查询结果插入到新表中。SET设置参数。SET CONSTRAINTS启用或禁用约束。SET ROLE设置当前用户的角色。SET SESSION AUTHORIZATION设置会话授权。SET TRANSACTION设置事务的属性。SHOW显示当前的参数设置。START TRANSACTION开始一个新的事务。TRUNCATE清空表的内容。CREATE APP WORKLOAD GROUP创建应用工作负载组。CREATE SNAPSHOT AS创建快照。EXECUTE执行预备的 SQL 语句。UPDATE更新表中的数据。CREATE TEXT SEARCH CONFIGURATION创建全文搜索配置。VACUUM释放被删除行占用的空间。ARCHIVE SNAPSHOT归档快照。BEGIN开始一个新的事务。CREATE MODEL创建模型。CREATE NODE GROUP创建节点组。CREATE OPERATOR创建新的自定义操作符。CREATE PACKAGE创建包。DROP APP WORKLOAD GROUP删除应用工作负载组。DROP APP WORKLOAD GROUP MAPPING删除应用工作负载组映射。DROP AUDIT POLICY删除审计策略。DROP CLIENT MASTER KEY删除客户端主密钥。DROP COLUMN ENCRYPTION KEY删除列加密密钥。DROP DATABASE删除数据库。DROP DIRECTORY删除目录。DROP Extension删除扩展。DROP FOREIGN TABLE删除外部表。DROP GROUP删除组。DROP INDEX删除索引。DROP MATERIALIZED VIEW删除材料化视图。DROP MODEL删除模型。DROP NODE删除节点。DROP NODE GROUP删除节点组。DROP OWNED删除指定所有者拥有的所有对象。DROP PACKAGE BODY删除包体。 -DROP RESOURCE LABEL删除资源标签。DROP ROW LEVEL SECURITY POLICY删除行级安全策略。DROP SCHEMA删除模式。DROP SEQUENCE删除序列。DROP SYNONYM删除同义词。DROP TABLE删除表。DROP TABLESPACE删除表空间。DROP TEXT SEARCH CONFIGURATION删除全文搜索配置。DROP TEXT SEARCH DICTIONARY删除全文搜索词典。DROP TRIGGER删除触发器。DROP TYPE删除用户自定义类型。DROP USER删除用户。DROP VIEW删除视图。EXECUTE DIRECT直接执行 SQL 命令。FETCH从光标中检索数据。GRANT授予用户或用户组特定的权限。INSERT将数据插入到表中。LOCK锁定表中的行。RESET重置参数。VALUES插入值到表中。 这些命令涵盖了 openGauss 中许多常用的 SQL 命令可以用于管理数据库对象、执行查询和修改数据等操作。
2. 数据类型
2.1数值类型
[表1] 列出了所有的可用类型。
表 1 整数类型 名称描述存储空间范围TINYINT微整数别名为INT1。1字节0 ~ 255SMALLINT小范围整数别名为INT2。2字节-32,768 ~ 32,767INTEGER常用的整数别名为INT4。4字节-2,147,483,648 ~ 2,147,483,647BINARY_INTEGER常用的整数INTEGER的别名。4字节-2,147,483,648 ~ 2,147,483,647BIGINT大范围的整数别名为INT8。8字节-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807int16十六字节的大范围整数目前不支持用户用于建表等使用。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,767SERIAL四字节序列整型。4字节-2,147,483,648 ~ 2,147,483,647BIGSERIAL八字节序列整型。8字节-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807LARGESERIAL十六字节序列整型。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.402E38~3.402E386位十进制数字精度。DOUBLE PRECISION,FLOAT8双精度浮点数不精准。8字节-1.79E308~1.79E30815位十进制数字精度。FLOAT[§]浮点数不精准。精度p取值范围为[1,53]。说明p为精度表示总位数。4字节或8字节根据精度p不同选择REAL或DOUBLE PRECISION作为内部表示。如不指定精度内部用DOUBLE PRECISION表示。BINARY_DOUBLE是DOUBLE PRECISION的别名。8字节-1.79E308~1.79E30815位十进制数字精度。DEC[(p[,s])]精度p取值范围为[1,1000]标度s取值范围为[0,p]。说明p为总位数s为小数位位数。用户声明精度。每四位十进制位占用两个字节然后在整个数据上加上八个字节的额外开销。未指定精度的情况下小数点前最大131,072位小数点后最大16,383位。INTEGER[(p[,s])]精度p取值范围为[1,1000]标度s取值范围为[0,p]。用户声明精度。每四位十进制位占用两个字节然后在整个数据上加上八个字节的额外开销。-
2.2 布尔类型
表 5 布尔类型 名称描述存储空间取值BOOLEAN布尔类型1字节。- true真
false假null未知unknown |
2.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字节。
2.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类型创建表。 2.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天数的精度取值范围为0~6。兼容性考虑目前未实现具体功能。
p秒数的精度取值范围为0~6。小数末尾的零不显示。 | 16字节 | | INTERVAL [FIELDS] [ § ] | 时间间隔。- fields可以是YEARMONTHDAYHOURMINUTESECONDDAY TO HOURDAY TO MINUTEDAY TO SECONDHOUR TO MINUTEHOUR TO SECONDMINUTE TO SECOND。- p秒数的精度取值范围为0~6且fields为SECONDDAY TO SECONDHOUR 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:sstimezone取值范围为1901-12-13 20:45:53 GMT~2038-01-18 23:59:59 GMT精度为秒。 | 4字节 |
2.6 几何类型
openGauss支持的几何类型请参见表10。最基本的类型点是其它类型的基础。
表 10 几何类型 名称存储空间说明表现形式point16字节平面中的点(x,y)lseg32字节有限线段((x1,y1),(x2,y2))box32字节矩形((x1,y1),(x2,y2))path1616n字节闭合路径与多边形类似((x1,y1),…)path1616n字节开放路径[(x1,y1),…]polygon4016n字节多边形与闭合路径相似((x1,y1),…)circle24 字节圆(x,y),r 圆心和半径
openGauss提供了一系列的函数和操作符用来进行各种几何计算如拉伸、转换、旋转、计算相交等。 点 点是几何类型的基本二维构造单位。用下面语法描述point的数值 ( x , y )
x , yx和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(x,y)表示圆心r表示半径。 圆的输出用第一种格式。
2.7 网络地址类型
openGauss提供用于存储IPv4、IPv6、MAC地址的数据类型。
用这些数据类型存储网络地址比用纯文本类型好因为这些类型提供输入错误检查和特殊的操作和功能。
表 11 网络地址类型 名称存储空间描述cidr7或19字节IPv4或IPv6网络inet7或19字节IPv4或IPv6主机和网络macaddr6字节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/yaddress表示IPv4或者IPv6地址y表示子网掩码的二进制位数。如果省略y则掩码部分使用已有类别的网络编号系统进行计算但要求输入的数据已经包括了确定掩码所需的所有字节。
表 12 cidr类型输入举例 cidr输入cidr输出abbrevcidr192.168.100.128/25192.168.100.128/25192.168.100.128/25192.168/24192.168.0.0/24192.168.0/24192.168/25192.168.0.0/25192.168.0.0/25192.168.1192.168.1.0/24192.168.1/24192.168192.168.0.0/24192.168.0/2410.1.210.1.2.0/2410.1.2/2410.110.1.0.0/1610.1/161010.0.0.0/810/810.1.2.3/3210.1.2.3/3210.1.2.3/322001:4f8:3:ba::/642001:4f8:3:ba::/642001:4f8:3:ba::/642001:4f8:3:ba:2e0:81ff:fe22:d1f1/1282001:4f8:3:ba:2e0:81ff:fe22:d1f1/1282001: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/yaddress表示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大小写都行。输出时都是以第一种形式展示。
2.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, B101, B00);--插入数据的长度不符合类型的标准会报错。
openGauss# INSERT INTO bit_type_t1 VALUES(2, B10, B101);
ERROR: bit string length 2 does not match type bit(3)
CONTEXT: referenced column: bt_col2--将不符合类型长度的数据进行转换。
openGauss# INSERT INTO bit_type_t1 VALUES(2, B10::bit(3), B101);--查看数据。
openGauss# SELECT * FROM bit_type_t1;bt_col1 | bt_col2 | bt_col3
---------------------------1 | 101 | 002 | 100 | 101
(2 rows)--删除表。
openGauss# DROP TABLE bit_type_t1;2.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 Joes contains a quote$$::tsvector;tsvector
------------------------------------------------Joes 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)2.10 UUID数据类型
UUID数据类型用来存储RFC 4122ISO/IEF 9834-8:2005以及相关标准定义的通用唯一标识符UUID。这个标识符是一个由算法产生的128位标识符确保它不可能使用相同算法在已知的模块中产生的相同标识符。
UUID是一个小写十六进制数字的序列由分字符分成几组一组8位数字三组4位数字一组12位数字总共32个数字代表128位标准的UUID示例如下
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11openGauss同样支持以其他方式输入大写字母和数字、由花括号包围的标准格式、省略部分或所有连字符、在任意一组四位数字之后加一个连字符。示例
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11一般是以标准格式输出。
2.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.5e2::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“” 和 strnull。对于数字当使用科学计数法的时候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 falsearray-jsonb类型长度长的 长度短的长度相等则依次比较每个元素。object-jsonb类型长度长的 长度短的长度相等则依次比较每个键值对先比较键在比较值。 注意 object-jsonb类型内比较时比较时使用的是格式整理后的最终结果进行比较因此相对于我们直接的输入未必会很直观。 创建索引、主外键 BTREE索引 jsonb类型支持创建btree索引支持创建主键、外键。 GIN索引 GIN索引可以用来有效地搜索出现在大量jsonb文档datums 中的键或者键/值对。提供了两个GIN操作符类(jsonb_ops、jsonb_hash_ops)提供了不同的性能和灵活性取舍。缺省的GIN操作符类支持使用、、?、 ?和?|操作符查询非缺省的GIN操作符类jsonb_path_ops只支持索引、操作符。 包含存在 查询一个JSON之中是否包含某些元素或者某些元素是否存在于某个JSON中是jsonb的一个重要能力。 -- 简单的标量/原始值只包含相同的值。
SELECT foo::jsonb 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; -- false2.11 HLL数据类型
HLLHyperLoglog是统计数据集中唯一值个数的高效近似算法。它有着计算速度快、节省空间的特点不需要直接存储集合本身而是存储一种名为HLL的数据结构。每当有新数据加入进行统计时只需要把数据经过哈希计算并插入到HLL中最后根据HLL就可以得到结果 和Redis的数据结构类似。
HLL与其他算法的比较请参见[表13]。
表 13 HLL与其他算法比较 项目Sort算法Hash算法HLL时间复杂度O(nlogn)O(n)O(n)空间复杂度O(n)O(n)log(logn)误差率00≈0.8%所需存储空间原始数据大小原始数据大小默认规格下最大16KB
HLL在计算速度和所占存储空间上都占优势。在时间复杂度上Sort算法需要排序至少O(nlogn)的时间虽说Hash算法和HLL一样扫描一次全表O(n)的时间就可以得出结果但是存储空间上Sort算法和Hash算法都需要先把原始数据存起来再进行统计会导致存储空间消耗巨大而对HLL来说不需要存原始数据只需要维护HLL数据结构故占用空间有很大的压缩默认规格下HLL数据结构的最大空间约为16KB。 须知 当前默认规格下可计算最大distinct值的数量约为1.1e15个误差率为0.8%。用户应注意如果计算结果超过当前规格下distinct最大值会导致计算结果误差率变大或导致计算结果失败并报错。用户在首次使用该特性时应该对业务的distinct value做评估选取适当的配置参数并做验证以确保精度符合要求当前默认参数下可以计算的distinct值为1.1e15如果计算得到的distinct值为NaN需要调整log2m或者采用其他算法计算distinct值。虽然hash算法存在极低的hash collision概率但是建议用户在首次使用时选取2-3个hash seed验证如果得到的distinct value相差不大则可以从该组seed中任选一个作为hash seed。 HLL中主要的数据结构请参见[表14]
表 14 HyperLogLog中主要数据结构 数据类型功能描述hllhll头部为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 t1Table public.t1Column | Type | Modifiers
----------------------------id | integer |set | hll |-- 创建hll类型的表指定前两个入参后两个采用默认值
openGauss# create table t2 (id integer, set hll(12,4));
openGauss# \d t2Table public.t2Column | 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 t3Table public.t3Column | 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 10HLL的应用场景。 场景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 factsgroup by 1;-- 计算每一天访问网站不同用户数量
openGauss# select date, hll_cardinality(users) from daily_uniques order by date;date | hll_cardinality
------------------------------2019-02-20 | 1002019-02-21 | 200.2179130593122019-02-22 | 301.764945080142019-02-23 | 400.8628583264462019-02-24 | 502.6269333496942019-02-25 | 601.9226064542132019-02-26 | 696.6023167694982019-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 2019-02-26::date;hll_cardinality
------------------696.602316769498
(1 row)-- 计算昨天访问过网站而今天没访问网站的用户数量。
openGauss# SELECT date, (#hll_union_agg(users) OVER two_days) - #users AS lost_uniques FROM daily_uniques WINDOW two_days AS (ORDER BY date ASC ROWS 1 PRECEDING); date | lost_uniques
--------------------------2019-02-20 | 02019-02-21 | 02019-02-22 | 02019-02-23 | 02019-02-24 | 02019-02-25 | 02019-02-26 | 02019-02-27 | 0
(8 rows)-- 删除表
openGauss# drop table facts;
openGauss# drop table daily_uniques;场景3“插入数据不满足hll数据结构要求” 当用户给hll类型的字段插入数据的时候必须保证插入的数据满足hll数据结构要求如果解析后不满足就会报错。如下示例中 插入数据’E\1234’时该数据不满足hll数据结构不能解析成功因此失败报错。 openGauss# create table test(id integer, set hll);
openGauss# insert into test values(1, E\1234);
ERROR: not a hll type, size6 is not enough
openGauss# drop table test;2.12 范围类型
范围类型是表达某种元素类型称为范围的_subtype_的一个值的范围的数据类型。例如timestamp的范围可以被用来表达一个会议室被保留的时间范围。在这种情况下数据类型是tsrange“timestamp range”的简写而timestamp是 subtype。subtype 必须具有一种总体的顺序这样对于元素值是在一个范围值之内、之前或之后就是界线清楚的。
范围类型非常有用因为它们可以表达一种单一范围值中的多个元素值并且可以很清晰地表达诸如范围重叠等概念。用于时间安排的时间和日期范围是最清晰的例子但是价格范围、一种仪器的量程等等也都有用。 内建范围类型 有下列内建范围类型 int4range — integer的范围int8range — bigint的范围numrange — numeric的范围tsrange — 不带时区的 timestamp的范围tstzrange — 带时区的 timestamp的范围daterange — date的范围 包含和排除边界 每一个非空范围都有两个界限下界和上界。上下界之间的所有值都被包括在范围内。一个包含界限意味着边界点本身也被包括在范围内而一个排除边界意味着边界点不被包括在范围内。 在一个范围的文本形式中一个包含下界被表达为“[”而一个排除下界被表达为“(”。同样一个包含上界被表达为“]”而一个排除上界被表达为“)”。 函数lower_inc和upper_inc分别测试一个范围值的上下界。 无限无界范围 一个范围的下界可以被忽略意味着所有小于上界的值都被包括在范围中例如(,3]。 同样如果范围的上界被忽略那么所有比上界大的值都被包括在范围中。如果上下界都被忽略该元素类型的所有值都被认为在该范围中。 规定缺失的包括界限自动转换为排除例如[,] 转换为 (,)。 你可以认为这些缺失值为 /- 无穷大但它们是特殊范围类型值并且被视为超出任何范围元素类型的 /- 无穷大值。 具有“infinity”概念的元素类型可以用它们作为显式边界值。例如在时间戳范围[today,infinity)不包括特殊的timestamp值infinity尽管 [today,infinity] 包括它就好比 [today,) 和 [today,]。 函数lower_inf和upper_inf分别测试一个范围的无限上下界。 范围输入输出 一个范围值的输入必须遵循下列模式之一 (lower-bound,upper-bound)
(lower-bound,upper-bound]
[lower-bound,upper-bound)
[lower-bound,upper-bound]
empty 圆括号或方括号指示上下界是否为排除的或者包含的。注意最后一个模式是empty它表示一个空范围一个不包含点的范围。 _lower-bound_可以是作为subtype的合法输入的一个字符串或者是空表示没有下界。同样_upper-bound_可以是作为 subtype 的合法输入的一个字符串或者是空表示没有上界。 每个界限值可以使用双引号字符引用。如果界限值包含圆括号、方括号、逗号、双引号或反斜线时这样做是必须的因为否则那些字符会被认作范围语法的一部分。要把一个双引号或反斜线放在一个被引用的界限值中就在它前面放一个反斜线还有在一个双引号引用的界限值中的一对双引号表示一个双引号字符这与 SQL 字符串中的单引号规则类似。此外你可以避免引用并且使用反斜线转义来保护所有数据字符否则它们会被当做返回语法的一部分。还有要写一个是空字符串的界限值则可以写成因为什么都不写表示一个无限界限。 范围值前后允许有空格但是圆括号或方括号之间的任何空格会被当做上下界值的一部分取决于元素类型它可能是也可能不是有意义的。 例子 -- 包括 3不包括 7并且包括 3 和 7 之间的所有点
SELECT [3,7)::int4range;
-- 既不包括 3 也不包括 7但是包括之间的所有点
SELECT (3,7)::int4range;
-- 只包括单独一个点 4
SELECT [4,4]::int4range;
-- 不包括点并且将被标准化为 空
SELECT [4,4)::int4range; 构造范围 每一种范围类型都有一个与其同名的构造器函数。使用构造器函数常常比写一个范围文字常数更方便因为它避免了对界限值的额外引用。构造器函数接受两个或三个参数。两个参数的形式以标准的形式构造一个范围下界是包含的上界是排除的而三个参数的形式按照第三个参数指定的界限形式构造一个范围。第三个参数必须是下列字符串之一 “()”、 “(]”、 “[)”或者 “[]”。 例如 -- 完整形式是下界、上界以及指示界限包含性/排除性的文本参数。
SELECT numrange(1.0, 14.0, (]);
-- 如果第三个参数被忽略则假定为 [)。
SELECT numrange(1.0, 14.0);
-- 尽管这里指定了 (]显示时该值将被转换成标准形式因为 int8range 是一种离散范围类型见下文。
SELECT int8range(1, 14, (]);
-- 为一个界限使用 NULL 导致范围在那一边是无界的。
SELECT numrange(NULL, 2.2); 离散范围类型 一种范围的元素类型具有一个良定义的“步长”例如integer或date。在这些类型中如果两个元素之间没有合法值它们可以被说成是相邻。这与连续范围相反连续范围中总是或者几乎总是可以在两个给定值之间标识其他元素值。例如numeric类型之上的一个范围就是连续的timestamp上的范围也是尽管timestamp具有有限的精度并且在理论上可以被当做离散的最好认为它是连续的因为通常并不关心它的步长。 另一种考虑离散范围类型的方法是对每一个元素值都有一种清晰的“下一个”或“上一个”值。了解了这种思想之后通过选择原来给定的下一个或上一个元素值来取代它就可以在一个范围界限的包含和排除表达之间转换。例如在一个整数范围类型中[4,8]和(3,9)表示相同的值集合但是对于 numeric 上的范围就不是这样。 一个离散范围类型应该具有一个_正规化_函数它知道元素类型期望的步长。正规化函数负责把范围类型的相等值转换成具有相同的表达特别是与包含或者排除界限一致。如果没有指定一个正规化函数那么具有不同格式的范围将总是会被当作不等即使它们实际上是表达相同的一组值。 内建的范围类型int4range、int8range和daterange都使用一种正规的形式该形式包括下界并且排除上界也就是[)。不过用户定义的范围类型可以使用其他习惯。 定义新的范围类型 用户可以定义他们自己的范围类型。这样做最常见的原因是为了使用内建范围类型中没有提供的 subtype 上的范围。例如要创建一个 subtype float8的范围类型 CREATE TYPE floatrange AS RANGE (subtype float8,subtype_diff float8mi
);
SELECT [1.234, 5.678]::floatrange; 因为float8没有有意义的“步长”我们在这个例子中没有定义一个正规化函数。 定义自己的范围类型也允许你指定使用一个不同的子类型 B-树操作符类或者集合 以便更改排序顺序来决定哪些值会落入到给定的范围中。 如果 subtype 被认为是具有离散值而不是连续值CREATE TYPE命令应当指定一个canonical函数。正规化函数接收一个输入的范围值并且必须返回一个可能具有不同界限和格式的等价的范围值。对于两个表示相同值集合的范围例如[1, 7]和[1, 8)正规的输出必须一样。选择哪一种表达作为正规的没有关系只要两个具有不同格式的等价值总是能被映射到具有相同格式的相同值就行。除了调整包含/排除界限格式外假使期望的补偿比 subtype 能够存储的要大一个正规化函数可能会舍入边界值。例如一个timestamp之上的范围类型可能被定义为具有一个一小时的步长这样正规化函数可能需要对不是一小时的倍数的界限进行舍入或者可能直接抛出一个错误。 另外任何打算要和 GiST 或 SP-GiST 索引一起使用的范围类型应当定一个 subtype 差异或subtype_diff函数没有subtype_diff时索引仍然能工作但是可能效率不如提供了差异函数时高。subtype 差异函数采用两个 subtype 输入值并且返回表示为一个float8值的差即_X_减_Y_。在我们上面的例子中可以使用常规float8减法操作符之下的函数。但是对于任何其他 subtype可能需要某种类型转换。还可能需要一些关于如何把差异表达为数字的创新型想法。为了最大的可扩展性subtype_diff函数应该同意选中的操作符类和排序规则所蕴含的排序顺序也就是说只要它的第一个参数根据排序顺序大于第二个参数它的结果就应该是正值。 subtype_diff函数的一个不那么过度简化的例子 CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS SELECT EXTRACT(EPOCH FROM (x - y)) LANGUAGE sql STRICT IMMUTABLE;
CREATE TYPE timerange AS RANGE ( subtype time, subtype_diff time_subtype_diff);
SELECT [11:10, 23:00]::timerange; 2.13 索引
可以为范围类型的表列创建 GiST 和 SP-GiST 索引。例如要创建一个 GiST 索引
CREATE INDEX reservation_idx ON reservation USING GIST (during); 一个 GiST 或 SP-GiST 索引可以加速涉及以下范围操作符的查询 、 、 、 、 «、 、 -|-、 以及 。
此外B-树和哈希索引可以在范围类型的表列上创建。对于这些索引类型基本上唯一有用的范围操作就是等值。使用相应的 和 操作符对于范围值定义有一种 B-树排序顺序但是该顺序相当任意并且在真实世界中通常不怎么有用。范围类型的 B-树和哈希支持主要是为了允许在查询内部进行排序和哈希而不是创建真正的索引。
2.14 对象标识符类型
openGauss在内部使用对象标识符OID作为各种系统表的主键。系统不会给用户创建的表增加一个OID系统字段OID类型代表一个对象标识符。
目前OID类型用一个四字节的无符号整数实现。因此不建议在创建的表中使用OID字段做主键。
表 15 对象标识符类型 名称引用描述示例OID-数字化的对象标识符。564182CID-命令标识符。它是系统字段cmin和cmax的数据类型。命令标识符是32位的量。-XID-事务标识符。它是系统字段xmin和xmax的数据类型。事务标识符也是64位的量。-TID-行标识符。它是系统表字段ctid的数据类型。行ID是一对数值块号块内的行索引它标识该行在其所在表内的物理位置。-REGCONFIGpg_ts_config文本搜索配置。englishREGDICTIONARYpg_ts_dict文本搜索字典。simpleREGOPERpg_operator操作符名。-REGOPERATORpg_operator带参数类型的操作符。*(integer,integer)或-(NONE,integer)REGPROCpg_proc函数名称。sumREGPROCEDUREpg_proc带参数类型的函数。sum(int4)REGCLASSpg_class关系名。pg_typeREGTYPEpg_type数据类型名。integer
OID类型主要作为数据库系统表中字段使用。
示例
openGauss# SELECT oid FROM pg_class WHERE relname pg_type;oid
------1247
(1 row)OID别名类型REGCLASS主要用于对象OID值的简化查找。
示例
openGauss# SELECT attrelid,attname,atttypid,attstattarget FROM pg_attribute WHERE attrelid pg_type::REGCLASS;attrelid | attname | atttypid | attstattarget
-----------------------------------------------1247 | xc_node_id | 23 | 01247 | tableoid | 26 | 01247 | cmax | 29 | 01247 | xmax | 28 | 01247 | cmin | 29 | 01247 | xmin | 28 | 01247 | oid | 26 | 01247 | ctid | 27 | 01247 | typname | 19 | -11247 | typnamespace | 26 | -11247 | typowner | 26 | -11247 | typlen | 21 | -11247 | typbyval | 16 | -11247 | typtype | 18 | -11247 | typcategory | 18 | -11247 | typispreferred | 16 | -11247 | typisdefined | 16 | -11247 | typdelim | 18 | -11247 | typrelid | 26 | -11247 | typelem | 26 | -11247 | typarray | 26 | -11247 | typinput | 24 | -11247 | typoutput | 24 | -11247 | typreceive | 24 | -11247 | typsend | 24 | -11247 | typmodin | 24 | -11247 | typmodout | 24 | -11247 | typanalyze | 24 | -11247 | typalign | 18 | -11247 | typstorage | 18 | -11247 | typnotnull | 16 | -11247 | typbasetype | 26 | -11247 | typtypmod | 23 | -11247 | typndims | 23 | -11247 | typcollation | 26 | -11247 | typdefaultbin | 194 | -11247 | typdefault | 25 | -11247 | typacl | 1034 | -1
(38 rows)2.15 伪类型
openGauss数据类型中包含一系列特殊用途的类型这些类型按照类别被称为伪类型。伪类型不能作为字段的数据类型但是可以用于声明函数的参数或者结果类型。
当一个函数不仅是简单地接受并返回某种SQL数据类型的情况下伪类型是很有用的。表16列出了所有的伪类型。
表 16 伪类型 名称描述any表示函数接受任何输入数据类型。anyelement表示函数接受任何数据类型。anyarray表示函数接受任意数组数据类型。anynonarray表示函数接受任意非数组数据类型。anyenum表示函数接受任意枚举数据类型。anyrange表示函数接受任意范围数据类型。cstring表示函数接受或者返回一个空结尾的C字符串。internal表示函数接受或者返回一种服务器内部的数据类型。language_handler声明一个过程语言调用句柄返回language_handler。fdw_handler声明一个外部数据封装器返回fdw_handler。record标识函数返回一个未声明的行类型。trigger声明一个触发器函数返回trigger。void表示函数不返回数值。opaque一个已经过时的类型以前用于所有上面这些用途。
声明用C编写的函数不管是内置的还是动态装载的都可以接受或者返回任何这样的伪数据类型。当伪类型作为参数类型使用时用户需要保证函数的正常运行。
用过程语言编写的函数只能使用实现语言允许的伪类型。目前过程语言都不允许使用作为参数类型的伪类型并且只允许使用void和record作为结果类型。一些多态的函数还支持使用anyelement、anyarray、anynonarray anyenum和anyrange类型。
伪类型internal用于声明那种只能在数据库系统内部调用的函数他们不能直接在SQL查询里调用。如果函数至少有一个internal类型的参数则不能从SQL里调用他。建议不要创建任何声明返回internal的函数除非他至少有一个internal类型的参数。
示例
--创建表
openGauss# create table t1 (a int);--插入两条数据
openGauss# insert into t1 values(1),(2);--创建函数showall()。
openGauss# CREATE OR REPLACE FUNCTION showall() RETURNS SETOF record
AS $$ SELECT count(*) from t1; $$
LANGUAGE SQL;--调用函数showall()。
openGauss# SELECT showall();showall
---------(2)
(1 row)--删除函数。
openGauss# DROP FUNCTION showall();--删除表
openGauss# drop table t1;2.16 列存表支持的数据类型
列存表支持的数据类型如[表17]所示。
表 17 列存表支持的数据类型 类别数据类型长度是否支持Numeric Typessmallint2支持integer4支持bigint8支持decimal-1支持numeric-1支持real4支持double precision8支持smallserial2支持serial4支持bigserial8支持largeserial-1支持Monetary Typesmoney8支持Character Typescharacter varying(n), varchar(n)-1支持character(n), char(n)n支持character、char1支持text-1支持nvarchar2-1支持name64不支持Date/Time Typestimestamp with time zone8支持timestamp without time zone8支持date4支持time without time zone8支持time with time zone12支持interval16支持big objectclob-1支持blob-1不支持other types……不支持
2.17 XML类型
openGauss支持XML类型使用示例如下。
openGauss CREATE TABLE xmltest ( id int, data xml );
openGauss INSERT INTO xmltest VALUES (1, one);
openGauss INSERT INTO xmltest VALUES (2, two);
openGauss SELECT * FROM xmltest ORDER BY 1;id | data
------------------------
1 | one
2 | two
(2 rows)
openGauss SELECT xmlconcat(, NULL, );
xmlconcat
(1 row)
openGauss SELECT xmlconcat(, NULL, );
xmlconcat
(1 row)说明 该功能默认未开启如需使用需要重新使用build.sh脚本编译数据库修改./configure配置参数在其中加入–with-libxml参数。在执行编译之前需要先执行yum install -y libxml2-devel否则会有configure: error: library ‘xml2’ (version 2.6.23) is required for XML support的报错。在执行编译之前需要三方库二进制文件中dependency操作系统环境/libobs/comm/lib加入到系统环境变量LD_LIBRARY_PATH中否则会报错libiconv.so不存在。 2.18 账本数据库使用的数据类型
账本数据库使用HASH16数据类型来存储行级hash摘要或表级hash摘要使用HASH32数据类型来存储全局hash摘要或者历史表校验hash。
表 18 账本数据库HASH类型 名称描述存储空间范围HASH16以无符号64位整数存储。8字节0 ~ 18446744073709551615HASH32以包含16个的无符号整型元素数的组存储。16字节16个元素的无符号整型数组能够包含的取值范围
HASH16数据类型用来在账本数据库中存储行级或表级hash摘要在获得长度为16个字符串的十六进制字符串的hash序列后系统将调用hash16in函数将该序列转换为一个无符号64位整数存储进HASH16类型变量中。示例如下
十六进制字符串e697da2eaa3a775b 对应的无符号64位整数16615989244166043483
十六进制字符串ffffffffffffffff 对应的无符号64位整数18446744073709551615HASH32数据类型用来在账本数据库中存储全局hash摘要或者历史表校验hash在获得长度为32个字符串的十六进制字符串的hash序列后系统将调用hash32in函数将该序列转换到一个包含16个无符号整型元素的数组中。示例如下
十六进制字符串685847ed1fe38e18f6b0e2b18c00edee
对应的HASH32数组[104,88,71,237,31,227,142,24,246,176,226,177,140,0,237,238]
