📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

• 前言
• • 📣 1.数值类型
  • • ✨ 1.1 整数类型
    • ✨ 1.2 任意精度型
    • ✨ 1.3 序列整型
    • ✨ 1.4 浮点类型
  • 📣 2. 布尔类型
  • 📣 3. 字符类型
  • 📣 4. 二进制类型
  • 📣 5. 日期/时间类型
  • • ✨ 5.1 日期输入
    • ✨ 5.2 时间段输入
  • 📣 6. 几何类型
  • 📣 7. 网络地址类型
  • 📣 8. 位串类型
  • 📣 9. 文本搜索类型
  • • ✨ 9.1 tsvector
    • ✨ 9.2 tsquery
  • 📣 10. UUID数据类型
  • 📣 11. JSON/JSONB类型
  • 📣 12. HLL数据类型
  • 📣 13. 范围类型
  • 📣 14.XML类型

前言

本篇介绍了openGauss的数据类型及应用

📣 1.数值类型

✨ 1.1 整数类型

✨ 1.2 任意精度型

✨ 1.3 序列整型

案例说明：
test=# CREATE TABLE smallserial_tab(a SMALLSERIAL);
–插入数据。
openGauss=# INSERT INTO smallserial_tab VALUES(default);
–再次插入数据。
openGauss=# INSERT INTO smallserial_tab VALUES(default);
test=# INSERT INTO smallserial_tab VALUES(8);
test=# INSERT INTO smallserial_tab VALUES(default);

✨ 1.4 浮点类型

📣 2. 布尔类型

📣 3. 字符类型

📣 4. 二进制类型

📣 5. 日期/时间类型

说明： 如果其他的数据库时间格式和openGauss的时间格式不一致，可通过修改配置参数DateStyle的值来保持一致。

案例示范：1.date类型openGauss=# CREATE TABLE date_type(coll date);openGauss=# INSERT INTO date_type VALUES (date '01-14-2023');openGauss=# select * from date_type; coll  --------------------- 2023-01-14 00:00:002.TIME/TIMESTAMP类型openGauss=# CREATE TABLE time_type (da time without time zone ,dai time with time zone,dfgh timestamp without time zone,dfga timestamp with time zone, vbg smalldatetime);openGauss=# INSERT INTO time_type VALUES ('21:21:22','21:21:22 pst','2022-12-12','2022-12-11 pst','2022-04-12 04:05:06');openGauss=# select * from time_type;    da    |     dai     | dfgh  |   dfga   |  vbg  ----------+-------------+---------------------+------------------------+--------------------- 21:21:22 | 21:21:22-08 | 2022-12-12 00:00:00 | 2022-12-11 16:00:00+08 | 2022-04-12 04:05:003.INTERVAL 类型openGauss=# CREATE TABLE day_type_tab (a int,b INTERVAL DAY(3) TO SECOND (4)); openGauss=# INSERT INTO day_type_tab VALUES (1, INTERVAL '3' DAY);openGauss=# select * from day_type_tab; a |   b    ---+-------- 1 | 3 days  openGauss=# CREATE TABLE year_type_tab(a int, b interval year (6));openGauss=# INSERT INTO year_type_tab VALUES(1,interval '2' year);openGauss=# SELECT * FROM year_type_tab; a |    b    ---+--------- 1 | 2 years

✨ 5.1 日期输入

系统支持按照日、月、年的顺序自定义日期输入。如果把DateStyle参数设置为MDY就按照“月-日-年”解析，设置为DMY就按照“日-月-年”解析，设置为YMD就按照“年-月-日”解析。
日期的文本输入需要加单引号包围，语法如下：
type [ ( p ) ] ‘value’
可选的精度声明中的p是一个整数，表示在秒域中小数部分的位数

--创建表。openGauss=# CREATE TABLE date_type_tab(coll date);--插入数据。openGauss=# INSERT INTO date_type_tab VALUES (date '12-10-2022');--查看数据。openGauss=# SELECT * FROM date_type_tab; coll  --------------------- 2022-12-10 00:00:00(1 row)--查看日期格式。openGauss=# SHOW datestyle; DateStyle ----------- ISO, MDY(1 row)--设置日期格式。openGauss=# SET datestyle='YMD';SET--插入数据。openGauss=# INSERT INTO date_type_tab VALUES(date '2022-12-11');--查看数据。openGauss=# SELECT * FROM date_type_tab; coll  --------------------- 2022-12-10 00:00:00 2022-12-11 00:00:00(2 rows)--删除表。openGauss=# DROP TABLE date_type_tab;

✨ 5.2 时间段输入

reltime的输入方式可以采用任何合法的时间段文本格式，包括数字形式（含负数和小数）及时间形式，其中时间形式的输入支持SQL标准格式、ISO-8601格式、POSTGRES格式等。另外，文本输入需要加单引号。

--创建表。openGauss=# CREATE TABLE reltime_type_tab(col1 character(30), col2 reltime);--插入数据。openGauss=# INSERT INTO reltime_type_tab VALUES ('90', '90');openGauss=# INSERT INTO reltime_type_tab VALUES ('-366', '-366');openGauss=# INSERT INTO reltime_type_tab VALUES ('1975.25', '1975.25');openGauss=# INSERT INTO reltime_type_tab VALUES ('-2 YEARS +5 MONTHS 10 DAYS', '-2 YEARS +5 MONTHS 10 DAYS');openGauss=# INSERT INTO reltime_type_tab VALUES ('30 DAYS 12:00:00', '30 DAYS 12:00:00');openGauss=# INSERT INTO reltime_type_tab VALUES ('P-1.1Y10M', 'P-1.1Y10M');--查看数据。openGauss=# SELECT * FROM reltime_type_tab;col1|  col2   --------------------------------+------------------------------------- 90 | 3 mons -366      | -1 years -18:00:00 1975.25   | 5 years 4 mons 29 days -2 YEARS +5 MONTHS 10 DAYS     | -1 years -6 mons -25 days -06:00:00 30 DAYS 12:00:00 | 1 mon 12:00:00 P-1.1Y10M | -3 mons -5 days -06:00:00--删除表。openGauss=# DROP TABLE reltime_type_tab;

📣 6. 几何类型

openGauss提供了一系列的函数和操作符用来进行各种几何计算，如拉伸、转换、旋转、计算相交等。

📣 7. 网络地址类型

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

📣 8. 位串类型

位串就是一串1和0的字符串。它们可以用于存储位掩码。
openGauss支持两种位串类型：bit(n)和bit varying(n)，这里的n是一个正整数。
bit类型的数据必须准确匹配长度n，如果存储短或者长的数据都会报错。
bit varying类型的数据是最长为n的变长类型，超过n的类型会被拒绝。一个没有长度的bit等效于bit(1)，
没有长度的bit varying表示没有长度限制。

📣 9. 文本搜索类型

openGauss提供了两种数据类型用于支持全文检索。tsvector类型表示为文本搜索优化的文件格式，tsquery类型表示文本查询。

tsvector类型表示一个检索单元，通常是一个数据库表中一行的文本字段或者这些字段的组合，tsvector类型的值是一个标准词位的有序列表，标准词位就是把同一个词的变型体都标准化成相同的，在输入的同时会自动排序和消除重复。to_tsvector函数通常用于解析和标准化文档字符串。

✨ 9.1 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'

✨ 9.2 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)

📣 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

📣 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;   -- sucselect '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; 布尔（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;  

📣 12. HLL数据类型

HLL（HyperLoglog）是统计数据集中唯一值个数的高效近似算法。它有着计算速度快，节省空间的特点，不需要直接存储集合本身，而是存储一种名为HLL的数据结构。每当有新数据加入进行统计时，只需要把数据经过哈希计算并插入到HLL中，最后根据HLL就可以得到结果。

-- 创建hll类型的表，不指定入参openGauss=# create table t1 (id integer, set hll);openGauss=# \d t1      Table "public.t1" Column |  Type   | Modifiers--------+---------+----------- c1     | integer | c2     | hll     |-- 创建hll类型的表，指定前两个入参，后两个采用默认值openGauss=# create table t2 (id integer, set hll(12,4));   Table "public.t2" Column |      Type      | Modifiers--------+----------------+----------- c1     | integer | c2     | 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--------+----------------+----------- c1     | integer | c2     | 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

📣 13. 范围类型

范围类型是表达某种元素类型（称为范围的_subtype_）的一个值的范围的数据类型。例如，timestamp的范围可以被用来表达一个会议室被保留的时间范围。在这种情况下，数据类型是tsrange（“timestamp range”的简写）而timestamp是 subtype。subtype 必须具有一种总体的顺序，这样对于元素值是在一个范围值之内、之前或之后就是界线清楚的。
范围类型非常有用，因为它们可以表达一种单一范围值中的多个元素值，并且可以很清晰地表达诸如范围重叠等概念。用于时间安排的时间和日期范围是最清晰的例子；但是价格范围、一种仪器的量程等等也都有用。

📣 14.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)