ios 分类原理 pg分类

转载

架构领航博主 2023-08-28 20:41:38

文章标签 ios 分类原理 postgresql sql json 数据类型 文章分类 iOS 移动开发

文章目录

前言
1、数字类型

1.1 数字类型列表
1.2 数字类型操作符和数学函数

2、字符类型

2.1 字符类型表
2.2 字符类型函数

3、时间/日期类型

3.1 时间/日期类型列表
3.2 时间/日期类型操作符
3.3 时间/日期类型常用函数

4、布尔类型
5、网络地址类型

5.1 网络地址类型列表
5.2 网络地址操作符
5.3 网络地址函数

6、数组类型

6.1 数组类型定义
6.2 数组类型值输入
6.3 查询数组元素
6.4 数组元素的追加、删除、更新
6.5 数组操作符
6.6 数组函数

7、范围类型

7.1 范围类型列表
7.2 范围类型边界
7.3 范围类型操作符
7.4 范围类型函数
7.5 给范围类型创建索引

8、json/jsonb类型

8.1 json类型简介
8.2 查询json数据
8.3 jsonb与json差异
8.4 jsonb与json操作符
8.5 jsonb与json函数
8.6 jsonb键/值的追加、删除、更新

9、数据类型转换

前言

本篇将介绍PostgreSQL的数据类型。PostgreSQL的数据类型非常丰富，本章将介绍常规数据类型和一些非常规数据类型，比如常规数据类型中的数字类型、字符类型、日期/时间类型等，非常规数据类型中的布尔类型、网络地址类型、数组类型、范围类型、json/jsonb类型等。介绍数据类型的同时也介绍数据类型相关操作符和函数，以及数据类型转换。

1、数字类型

PostgreSQL支持的数字类型有整数类型、用户指定精度类型、浮点类型、serial类型。

1.1 数字类型列表

PG支持的数字类型如表：

ios 分类原理 pg分类_ios 分类原理

smallint、integer、bigint都是整数类型，存储一定范围的整数，超出范围将会报错。smallint存储2字节整数，字段定义时可写成int2，integer存储4字节整数，支持的数值范围比 smallint大，字段定义时可写成int4，是最常用的整数类型， bigint存储8字节整数，支持的数值范围比integer大，字段定义时可写成int8。对于大多数使用整数类型的场景使用integer就够了，除非integer范围不够用的情况下才使用bigint。定义一张使用integer类型的表如下所示：

mydb=> CREATE TABLE test_integer (id1 integer,id2 int4) ; 
CREATE TABLE

decimal和numeric是等效的，可以存储指定精度的多位数据，比如带小数位的数据，适用于要求计算准确的数值运算，声明numeric的语法如下所示：

NUMERIC(precision, scale)

precision是指numeric数字里的全部位数，scale是指小数部分的数字位数，例如18.222的precision为5，而scale为3；
precision必须为正整数，scale可以是0或整数，由于numeric类型上的算术运算相比整数类型性能低，因此，如果两种数据类型都能满足业务需求，从性能上考虑不建议使用numeric数据类型。
real和double precision是指浮点数据类型，real支持4字节， double precision支持8字节，浮点数据类型在实际生产案例的使用相比整数类型会少些。 smallserial、serial和 bigserial类型是指自增serial类型，严格意义上不能称之为一种数据类型，如下代码创建一张测试表，定义test_serial表的id字段为serial类型：

mydb=> CREATE TABLE test_serial (id serial,flag text); 
CREATE TABLE

插入表数据时可以不指定serial字段名称，将自动使用序列值填充，如下所示：

mydb=> INSERT INTO test_serial(flag) VALUES ('a'); 
INSERT 0 1 
mydb=> INSERT INTO test_serial(flag) VALUES ('b'); 
INSERT 0 1 
mydb=> INSERT INTO test_serial(flag) VALUES ('c'); 
INSERT 0 1 
mydb=> SELECT * FROM test_serial; 
    id | flag 
-------+------ 
     1 | a 
     2 | b 
     3 | c 
(3 rows)

1.2 数字类型操作符和数学函数

PostgreSQL支持数字类型操作符和丰富的数学函数，例如支持加、减、乘、除、模取余操作符，如下所示：

mydb=> SELECT 1+2,2*3,4/2,8%3; 
   ?column?  | ?column? | ?column? | ?column?
-------------+----------+----------+---------- 
           3 |        6 |        2 |       2

按模取余如下所示：

mydb=> SELECT mod(8,3); 
 mod 
----- 
  2
(1 row)

四舍五入函数如下所示：

mydb=> SELECT round(10.2),round(10.9); 
    round | round 
----------+------- 
       10 | 11 
(1 row)

返回大于或等于给出参数的最小整数，如下所示：

mydb=> SELECT ceil(3.6),ceil(-3.6); 
    ceil | ceil 
---------+------ 
       4 | -3 
(1 row)

返回小于或等于给出参数的最大整数，如下所示：

mydb=> SELECT floor(3.6), floor(-3.6); 
    floor | floor 
----------+------- 
        3 | -4
(1 row)

2、字符类型

2.1 字符类型表

ios 分类原理 pg分类_数据类型_02

character varying（n）存储的是变长字符类型，n是一个正整数，如果存储的字符串长度超出n则报错；如果存储的字符串长度比n小，character varying（n）仅存储字符串的实际位数。character（n）存储定长字符，如果存储的字符串长度超出n则报错；如果存储的字符串长度比n小，则用空白填充。为了验证此特性，下面做个实验，创建一张测试表，并插入一条测试数据，代码如下所示：

mydb=> CREATE TABLE test_char(col1 varchar (4),col2 character(4)); 
CREATE TABLE 
mydb=> INSERT INTO test_char(col1,col2) VALUES ('a','a'); 
INSERT 0 1

表test_char的字段col1类型为character varying（4），col2 类型为character（4），接下来计算两个字段值的字符串长
度，代码如下所示：

mydb=> SELECT char_length(col1),char_length(col2) FROM test_char ; 
    char_length | char_length 
----------------+------------- 
              1 | 1 
(1 row)

char_length（string）显示字符串字符数，从上面结果可以看出字符串长度都为1，接着查看两字段实际占用的物理空间大小，代码如下所示：

mydb=> SELECT octet_length(col1),octet_length(col2) FROM test_char ; 
     octet_length | octet_length 
 -----------------+-------------- 
                1 | 4 
 (1 row)

octet_length（string）显示字符串占用的字节数，col2字段占用了4个字节，正好是col2字段定义的character长度。值得一提的是character varying（n）类型如果不声明长度，将存储任意长度的字符串，而character（n）如果不声明长度则等效于character（1）。

text字符类型存储任意长度的字符串，和没有声明字符长度的character varying字符类型几乎没有差别。

提示：
PostgreSQL支持最大的字段大小为1GB，虽然文档上说没有声明长度的character varying和text都支持任意长度的字符串，但仍受最大字段大小1GB的限制；此外，从性能上考虑这两种字符类型几乎没有差别，只是character（n）类型当存储的字符串长度不够时会用空白填充，这将带来存储空间一定程度的浪费，使用时需注意。

2.2 字符类型函数

PostgreSQL支持丰富的字符函数，下面举例说明。计算字符串中的字符数，如下所示：

mydb=> SELECT char_length('abcd'); 
 char_length 
------------- 
	4 
(1 row)

计算字符串占用的字节数，如下所示：

mydb=> SELECT octet_length('abcd'); 
 octet_length 
-------------- 
	4 
(1 row)

指定字符在字符串的位置，如下所示：

mydb=> SELECT position('a' in 'abcd'); 
 position 
 ----------
 	1 
 (1 row)

提取字符串中的子串，如下所示：

mydb=> SELECT substring('francs' from 3 for 4); 
substring
----------- 
ancs (1 row)

拆分字符串，split_part函数语法如下：

split_part(string text, delimiter text, field int)

根据delimiter分隔符拆分字符串string，并返回指定字段，字段从1开始，如下所示：

mydb=> SELECT split_part('abc@def1@nb','@',2); 
 split_part 
 ------------ 
 	def1 
 (1 row)

3、时间/日期类型

PostgreSQL对时间、日期数据类型的支持丰富而灵活，本节介绍PostgreSQL支持的时间、日期类型，及其操作符和常用函数。

3.1 时间/日期类型列表

ios 分类原理 pg分类_json_03

我们通过一个简单的例子理解这几个时间、日期数据类型，先来看看系统自带的now（）函数，now（）函数显示当前时间，返回的类型为timestamp[（p）]with time zone，如下所示：

mydb=> SELECT now(); 
		now 
------------------------------- 
2017-07-29 09:44:25.493425+08 
(1 row)

这里提前介绍下类型转换，本篇最后一节将专门介绍数据类型转换的常用方法，以下SQL中的两个冒号是指类型转换，转换成timestamp without time zone格式如下，注意返回的数据变化：

mydb=> SELECT now()::timestamp without time zone; 
	now
 ---------------------------- 
 2017-07-29 09:44:55.804403 
 (1 row)

转换成date格式，如下所示：

mydb=> SELECT now()::date; 
  	now 
------------ 
 2017-07-29 
(1 row)

转换成time without time zone，如下所示：

mydb=> SELECT now()::time without time zone; 
 	now 
 ----------------- 
 09:45:49.390428 
 (1 row)

转换成time with time zone，如下所示：

mydb=> SELECT now():: time with time zone; 
 	now 
 -------------------
09:45:57.13139+08 
(1 row)

interval指时间间隔，时间间隔单位可以是hour、day、 month、year等，举例如下：

mydb=> SELECT now(),now()+interval'1 day'; 
                              now | ?column? 
----------------------------------+------------------------------- 
    2017-07-29 09:47:26.026418+08 | 2017-07-30 09:47:26.026418+08 
(1 row)

通过以上几个示例对时间、日期数据类型有个初步的了解，值得一提的是时间类型中的（p）是指时间精度，具体指秒后面小数点保留的位数，如果没声明精度默认值为 6，以下示例声明精度为0：

mydb=> SELECT now(), now()::timestamp(0); 
                              now | now 
----------------------------------+--------------------- 
    2017-07-29 09:59:42.688445+08 | 2017-07-29 09:59:43 
(1 row)

3.2 时间/日期类型操作符

时间、日期数据类型支持的操作符有加、减、乘、除，下面举例说明。日期相加，如下所示：

mydb=> SELECT date '2017-07-29' + interval'1 days';
	?column? 
--------------------- 
2017-07-30 00:00:00 
(1 row)

日期相减，如下所示：

mydb=> SELECT date '2017-07-29' - interval'1 hour'; 
	?column? 
--------------------- 
2017-07-28 23:00:00 
(1 row)

日期相乘，如下所示：

mydb=> SELECT 100* interval '1 second'; 
 ?column? 
---------- 
00:01:40 
(1 row)

日期相除，如下所示：

mydb=> SELECT interval '1 hour' / double precision '3'; 
 ?column? 
---------- 
00:20:00 
(1 row)

3.3 时间/日期类型常用函数

接下来演示时间、日期常用函数。
显示当前时间，如下所示：

mydb=> SELECT current_date, current_time; 
    current_date | current_time 
-----------------+-------------------- 
      2017-07-29 | 10:53:10.375374+08 
(1 row)

另一个非常重要的函数为EXTRACT函数，可以从日期、时间数据类型中抽取年、月、日、时、分、秒信息，语法如下所示：EXTRACT(field FROM source) field值可以为century、year、month、day、hour、minute、 second等，source类型为timestamp、time、interval的值的表达式，例如取年份，代码如下所示：

mydb=> SELECT EXTRACT( year FROM now()); 
 date_part 
----------- 
 2017 
(1 row)

对于timestamp类型，取月份和月份里的第几天，代码如下所示：

mydb=> SELECT EXTRACT( month FROM now()),EXTRACT(day FROM now()); 
    date_part | date_part 
--------------+----------- 
            7 | 29 
(1 row)

取小时、分钟，如下所示：

mydb=> SELECT EXTRACT( hour FROM now()), extract (minute FROM now()); 
    date_part | date_part 
--------------+----------- 
           11 | 14

取秒，如下所示：

mydb=> SELECT EXTRACT( second FROM now()); 
 date_part 
----------- 
 43.031366 
(1 row)

取当前日期所在年份中的第几周，如下所示：

mydb=> SELECT EXTRACT( week FROM now()); 
date_part 
----------- 
30 
(1 row)

当天属于当年的第几天，如下所示：

mydb=> SELECT EXTRACT( doy FROM now()); 
  date_part 
----------- 
  210 
(1 row)

4、布尔类型

前三小节介绍了PostgreSQL支持的数字类型、字符类型、时间日期类型，这些数据类型是关系型数据库的常规数据类型，此外PostgreSQL还支持很多非常规数据类型，比如布尔类型、网络地址类型、数组类型、范围类型、json/jsonb类型等，从这一节开始将介绍PostgreSQL支持的非常规数据类型，本节介绍布尔类型，PostgreSQL支持的布尔类型如表

ios 分类原理 pg分类_json_04

true状态的有效值可以是TRUE、t、true、y、yes、on、 1；false状态的有效值为FALSE、f、false、n、no、off、0，首先创建一张表来进行演示，如下所示:

mydb=> CREATE TABLE test_boolean(cola boolean,colb boolean);
CREATE TABLE 
mydb=> INSERT INTO test_boolean (cola,colb) VALUES ('true','false'); 
INSERT 0 1 
mydb=> INSERT INTO test_boolean (cola,colb) VALUES ('t','f'); I
NSERT 0 1 
mydb=> INSERT INTO test_boolean (cola,colb) VALUES ('TRUE','FALSE'); 
INSERT 0 1 
mydb=> INSERT INTO test_boolean (cola,colb) VALUES ('yes','no'); 
INSERT 0 1 
mydb=> INSERT INTO test_boolean (cola,colb) VALUES ('y','n'); 
INSERT 0 1 
mydb=> INSERT INTO test_boolean (cola,colb) VALUES ('1','0'); 
INSERT 0 1 
mydb=> INSERT INTO test_boolean (cola,colb) VALUES (null,null); 
INSERT 0 1

查询表test_boolean数据，尽管有多样的true、false状态输入值，查询表布尔类型字段时true状态显示为t，false状态显示为f，并且可以插入NULL字符，查询结果如下所示：

mydb=> SELECT * FROM test_boolean ; 
	cola | colb 
---------+------ 
	   t | f 
	   t | f 
	   t | f 
	   t | f 
	   t | f 
	   t | f 
         | 
(7 rows)

5、网络地址类型

当有存储IP地址需求的业务场景时，对于PostgreSQL并不很熟悉的开发者可能会使用字符类型存储，实际上PostgreSQL 提供用于存储IPv4、IPv6、MAC网络地址的专有网络地址数据类型，使用网络地址数据类型存储IP地址要优于字符类型，因为网络地址类型一方面会对数据合法性进行检查，另一方面也提供了网络数据类型操作符和函数方便应用程序开发。

5.1 网络地址类型列表

ios 分类原理 pg分类_数据类型_05

inet和cidr类型存储的网络地址格式为address/y，其中 address表示IPv4或IPv6网络地址，y表示网络掩码位数，如果y 省略，则对于IPv4网络掩码为32，对于IPv6网络掩码为128，所以该值表示一台主机。 inet和cidr类型都会对数据合法性进行检查，如果数据不合法会报错，如下所示：

mydb=> SELECT '192.168.2.1000'::inet; 
ERROR: invalid input syntax for type inet: "192.168.2.1000"
LINE 1: select '192.168.2.1000'::inet;

inet和cidr网络类型存在以下差别。
1）cidr类型的输出默认带子网掩码信息，而inet不一定，如下所示：

mydb=> SELECT '192.168.1.100'::cidr; 
	cidr 
------------------ 
192.168.1.100/32 
(1 row) 

mydb=> SELECT '192.168.1.100/32'::inet; 
	inet 
--------------- 
192.168.1.100 
(1 row) 

mydb=> SELECT '192.168.0.0/16'::inet; 
	inet 
---------------- 
192.168.0.0/16 
(1 row)

2）cidr类型对IP地址和子网掩码合法性进行检查，而inet 不会，如下所示：

mydb=> SELECT '192.168.2.0/8'::cidr; 
ERROR: invalid cidr value: "192.168.2.0/8" LINE 1: select '192.168.2.0/8'::cidr; DETAIL: Value has bits set to right of mask. 

mydb=> SELECT '192.168.2.0/8'::inet; 
	inet 
--------------- 
192.168.2.0/8
(1 row)

mydb=> SELECT '192.168.2.0/24'::cidr; 
cidr 
---------------- 
192.168.2.0/24 
(1 row)

因此，从这个层面来说cidr比inet网络类型更严谨。 macaddr和macaddr8存储MAC地址，这里不做介绍。

5.2 网络地址操作符

PostgreSQL支持丰富的网络地址数据类型操作符，如表:

ios 分类原理 pg分类_sql_06

5.3 网络地址函数

PostgreSQL网络地址类型支持一系列内置函数，下面举例说明。取IP地址，返回文本格式，如下所示：

mydb=> SELECT host(cidr '192.168.1.0/24'); 
	host 
------------- 
192.168.1.0 
(1 row)

取IP地址和网络掩码，返回文本格式，如下所示：

mydb=> SELECT text(cidr '192.168.1.0/24'); 
	text 
---------------- 
192.168.1.0/24 
(1 row)

取网络地址子网掩码，返回文本格式，如下所示：

mydb=> SELECT netmask(cidr '192.168.1.0/24'); 
	netmask 
--------------- 
255.255.255.0

6、数组类型

PostgreSQL支持一维数组和多维数组，常用的数组类型为数字类型数组和字符型数组，也支持枚举类型、复合类型数组。

6.1 数组类型定义

先来看看数组类型的定义，创建表时在字段数据类型后面加方括号“[]”即可定义数组数据类型，如下所示：

CREATE TABLE test_array1 ( 
    id integer, 
    array_i integer[], 
    array_t text[] 
);

以上integer[]表示integer类型一维数组，text[]表示text类型一维数组。

6.2 数组类型值输入

数组类型的插入有两种方式，第一种方式使用花括号方式，如下所示：

'{ val1 delim val2 delim ... }'

将数组元素值用花括号“{}”包围并用delim分隔符分开，数组元素值可以用双引号引用，delim分隔符通常为逗号，如下所示：

mydb=> SELECT '{1,2,3}'; 
 ?column? 
---------- 
 {1,2,3} 
(1 row)

往表test_array1中插入一条记录的代码如下所示：

mydb=> INSERT INTO test_array1(id,array_i,array_t) 
VALUES (1,'{1,2,3}','{"a","b","c"}'); 
INSERT 0 1

数组类型插入的第二种方式为使用ARRAY关键字，例如：

mydb=> SELECT array[1,2,3]; 
 array 
--------- 
 {1,2,3} 
(1 row)

往test_array2表中插入另一条记录，代码如下所示：

mydb=> INSERT INTO test_array1(id,array_i,array_t) 
VALUES (2,array[4,5,6],array['d','e','f']); 
INSERT 0 1

表test_array2的数据如下所示：

mydb=> SELECT * FROM test_array1; 
id | array_i | array_t 
-------+---------+--------- 
1 | {1,2,3} | {a,b,c} 2 | {4,5,6} | {d,e,f} 
(2 rows)

6.3 查询数组元素

如果想查询数组所有元素值，只需查询数组字段名称即可，如下所示：

mydb=> SELECT array_i FROM test_array1 WHERE id=1; 
 array_i 
--------- 
 {1,2,3} 
(1 row)

数组元素的引用通过方括号“[]”方式，数据下标写在方括号内，编号范围为1到n，n为数组长度，如下所示：

mydb=> SELECT array_i[1],array_t[3] FROM test_array1 WHERE id=1; 
    array_i | array_t 
------------+--------- 
          1 | c 
(1 row)

6.4 数组元素的追加、删除、更新

PostgreSQL数组类型支持数组元素的追加、删除与更新操作，数组元素的追加使用array_append函数，用法如下所示：
array_append(anyarray, anyelement) array_append函数向数组末端追加一个元素，如下所示：

mydb=> SELECT array_append(array[1,2,3],4); 
 array_append 
-------------- 
 {1,2,3,4} 
(1 row)

数据元素追加到数组也可以使用操作符||，如下所示：

mydb=> SELECT array[1,2,3] || 4; 
 ?column? 
----------- 
 {1,2,3,4} 
(1 row)

数组元素的删除使用array_remove函数，array_remove函数用法如下所示： array_remove(anyarray, anyelement) array_remove函数将移除数组中值等于给定值的所有数组元素，如下所示：

mydb=> SELECT array[1,2,2,3],array_remove(array[1,2,2,3],2); 
      array | array_remove 
------------+-------------- 
  {1,2,2,3} | {1,3} 
(1 row)

数组元素的修改代码如下所示：

mydb=> UPDATE test_array1 SET array_i[3]=4 WHERE id=1 ; 
UPDATE 1

整个数组也能被更新，如下所示：

mydb=> UPDATE test_array1 SET array_i=array[7,8,9] WHERE id=1; 
UPDATE 1

6.5 数组操作符

PostgreSQL数组元素支持丰富操作符，如表所示。

ios 分类原理 pg分类_sql_07

6.6 数组函数

PostgreSQL支持丰富的数组函数，给数组添加元素或删除元素，如下所示：

mydb=> SELECT array_append(array[1,2],3),array_remove(array[1,2],2); 
    array_append | array_remove 
-----------------+-------------- 
         {1,2,3} | {1} 
(1 row)

获取数组维度，如下所示：

mydb=> SELECT array_ndims(array[1,2]); 
 array_ndims 
------------- 
	1 
(1 row)

获取数组长度，如下所示：

mydb=> SELECT array_length(array[1,2],1); 
 array_length 
-------------- 
	2 
(1 row)

返回数组中某个数组元素第一次出现的位置，如下所示：

mydb=> SELECT array_position(array['a','b','c','d'],'d'); 
 array_position 
---------------- 
	4
(1 row)

数组元素替换可使用函数array_replace，语法如下： array_replace(anyarray, anyelement, anyelement) 函数返回值类型为anyarray，使用第二个anyelement替换数组中的相同数组元素，如下所示：

mydb=> SELECT array_replace(array[1,2,5,4],5,10); 
 array_replace 
--------------- 
 {1,2,10,4} 
(1 row)

将数组元素输出到字符串，可以使用array_to_string函数，语法如下： atray_to_string(anyarray, text [, text]) 函数返回值类型为text，第一个text参数指分隔符，第二个 text表示将值为NULL的元素使用这个字符串替换，示例如下：

mydb=> SELECT array_to_string(array[1,2,null,3],',','10'); 
array_to_string 
----------------- 
1,2,10,3 
(1 row)

7、范围类型

范围类型包含一个范围内的数据，常见的范围数据类型有日期范围类型、整数范围类型等；范围类型提供丰富的操作符和函数，对于日期安排、价格范围应用场景比较适用。

7.1 范围类型列表

PostgreSQL系统提供内置的范围类型如下：

int4range—integer范围类型
int8range—bigint范围类型
numrange—numeric范围类型
tsrange—不带时区的timestamp范围类型
tstzrange—带时区的timestamp范围类型
daterange—date范围类型

用户也可以通过CREATE TYPE命令自定义范围数据类型，integer范围类型举例如下：

mydb=> SELECT int4range(1,5); 
 int4range 
----------- 
  [1,5) 
(1 row)

以上定义1到5的整数范围，date范围类型举例如下：

mydb=> SELECT daterange('2017-07-01','2017-07-30'); 
	daterange 
------------------------- 
[2017-07-01,2017-07-30)

7.2 范围类型边界

每一个范围类型都包含下界和上界，方括号"【"表示包含下界，圆括号“（”表示排除下界，方括号“]”表示包含上界，圆括号“）”表示排除上界，也就是说方括号表示边界点包含在内，圆括号表示边界点不包含在内，范围类型值的输入有以下几种模式：

(lower-bound,upper-bound) 
 (lower-bound,upper-bound]
 [lower-bound,upper-bound)
 [lower-bound,upper-bound] 
 empty

注意empty表示空范围类型，不包含任何元素，看下面这个例子：

mydb=> SELECT int4range(4,7); 
 int4range 
----------- 
  [4,7) 
(1 row)

以上表示包含4、5、6，但不包含7，标准的范围类型为下界包含同时上界排除，如下所示：

mydb=> SELECT int4range(1,3); 
 int4range 
----------- 
  [1,3) 
(1 row)

以上没有指定数据类型边界模式，指定上界为“]”，如下所示：

mydb=> SELECT int4range(1,3,'[]'); 
 int4range 
----------- 
  [1,4) 
(1 row)

虽然指定上界“]”，但上界依然显示为“）”，这是范围类型标准的边界模式，即下界包含同时上界排除，这点需要注意。

7.3 范围类型操作符

本节介绍常见的范围类型操作符。包含元素操作符，如下所示：

mydb=> SELECT int4range(4,7) @> 4; 
?column? 
---------- 
   t 
(1 row)

包含范围操作符，如下所示：

mydb=> SELECT int4range(4,7)@>int4range(4,6); 
?column? 
---------- 
 t 
(1 row)

等于操作符，如下所示：

mydb=> SELECT int4range(4,7)=int4range(4,6,'[]'); 
?column? 
---------- 
	t 
(1 row)

其中“@>”操作符在范围数据类型中比较常用，常用来查询范围数据类型是否包含某个指定元素，由于篇幅关系，其他范围数据类型操作符这里不演示了。

7.4 范围类型函数

以下列举范围类型常用函数，例如，取范围下界，如下所示：

mydb=> SELECT lower(int4range(1,10)); 
lower 
------- 
  1 
(1 row)

取范围上界，如下所示：

mydb=> SELECT upper(int4range(1,10)); 
upper 
------- 
  10 
(1 row)

范围是否为空？示例如下：

mydb=> SELECT isempty(int4range(1,10)); 
isempty 
--------- 
	f 
(1 row)

7.5 给范围类型创建索引

范围类型数据支持创建GiST索引，GiST索引支持的操作符有“=”“&&”“<@”“@>”“<<”“>>”“-|-”“&<”“&>”等，GiST索引创建举例如下：

CREATE INDEX idx_ip_address_range ON ip_address USING gist ( ip_range);

8、json/jsonb类型

PostgreSQL不只是一个关系型数据库，同时它还支持非关系数据类型json（JavaScript Object Notation），json属于重量级的非常规数据类型，本节将介绍json类型、json与jsonb差异、json与jsonb操作符和函数，以及jsonb键值的追加、删除、更新。

8.1 json类型简介

PostgreSQL早在9.2版本已经提供了json类型，并且随着大版本的演进，PostgreSQL对json的支持趋于完善，例如提供更多的json函数和操作符方便应用开发，一个简单的json类型例子如下：

mydb=> SELECT '{"a":1,"b":2}'::json; 
	json 
--------------- 
{"a":1,"b":2}

为了更好地演示json类型，接下来创建一张表，如下所示：

mydb=> CREATE TABLE test_json1 (id serial primary key,name json);

CREATE TABLE 以上示例定义字段name为json类型，插入表数据，如下所示：

mydb=> INSERT INTO test_json1 (name) VALUES ('{"col1":1,"col2":"francs","col3":"male"}'); 
INSERT 0 1 
mydb=> INSERT INTO test_json1 (name) VALUES ('{"col1":2,"col2":"fp","col3":"female"}'); 
INSERT 0 1

查询表test_json1数据，如下所示：

mydb=> SELECT * FROM test_json1; 
    id | name 
-------+------------------------------------------ 
     1 | {"col1":1,"col2":"francs","col3":"male"} 
     2 | {"col1":2,"col2":"fp","col3":"female"}

8.2 查询json数据

通过“->”操作符可以查询json数据的键值，如下所示：

mydb=> SELECT name -> 'col2' FROM test_json1 WHERE id=1; 
 ?column? 
---------- 
 "francs" 
(1 row)

如果想以文本格式返回json字段键值可以使用“->>”操作符，如下所示：

mydb=> SELECT name ->> 'col2' FROM test_json1 WHERE id=1; 
 ?column? 
 ---------- 
  francs 
 (1 row)

8.3 jsonb与json差异

PostgreSQL支持两种JSON数据类型：json和jsonb，两种类型在使用上几乎完全相同，两者主要区别为以下：json存储格式为文本而jsonb存储格式为二进制，由于存储格式的不同使得两种json数据类型的处理效率不一样，json类型以文本存储并且存储的内容和输入数据一样，当检索json数据时必须重新解析，而jsonb以二进制形式存储已解析好的数据，当检索jsonb数据时不需要重新解析，因此json写入比jsonb快，但检索比jsonb慢，后面会通过测试验证两者读写性能的差异。除了上述介绍的区别之外，json与jsonb在使用过程中还存在差异，例如jsonb输出的键的顺序和输入不一样，如下所示：

mydb=> SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb; 
				jsonb 
-------------------------------------------------- 
{"bar": "baz", "active": false, "balance": 7.77} 
(1 row)

而json的输出键的顺序和输入完全一样，如下所示：

mydb=> SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
    			json 
 ------------------------------------------------- 
{"bar": "baz", "balance": 7.77, "active":false} 
(1 row)

另外，jsonb类型会去掉输入数据中键值的空格，如下所示：

mydb=> SELECT ' {"id":1, "name":"francs"}'::jsonb; 
			jsonb 
----------------------------- 
{"id": 1, "name": "francs"} 
(1 row)

上例中id键与name键输入时是有空格的，输出显示空格键被删除，而json的输出和输入一样，不会删掉空格键：

mydb=> SELECT ' {"id":1, "name":"francs"}'::json; 
			json 
------------------------------- 
{"id":1, "name":"francs"} 
(1 row)

另外，jsonb会删除重复的键，仅保留最后一个，如下所示：

mydb=> SELECT ' {"id":1, "name":"francs", "remark":"a good guy!", "name":"test" }'::jsonb; 
					jsonb 
---------------------------------------------------- 
{"id": 1, "name": "test", "remark": "a good guy!"} 
(1 row)

上面name键重复，仅保留最后一个name键的值，而json数据类型会保留重复的键值。在大多数应用场景下建议使用jsonb，除非有特殊的需求，
比如对json的键顺序有特殊的要求。

8.4 jsonb与json操作符

以文本格式返回json类型的字段键值可以使用“->>”操作符，如下所示：

mydb=> SELECT name ->> 'col2' FROM test_json1 WHERE id=1; 
 ?column? 
 ---------- 
 francs 
 (1 row)

字符串是否作为顶层键值，如下所示：

mydb=> SELECT '{"a":1, "b":2}'::jsonb ? 'a'; 
 ?column? 
 ---------- 
 	t 
(1 row)

删除json数据的键/值，如下所示：

mydb=> SELECT '{"a":1, "b":2}'::jsonb - 'a'; 
 ?column? 
---------- 
 {"b": 2} 
(1 row)

8.5 jsonb与json函数

json与jsonb相关的函数非常丰富，下面举例说明。扩展最外层的json对象成为一组键/值结果集，如下所示：

mydb=> SELECT * FROM json_each('{"a":"foo", "b":"bar"}'); 
    key | value 
--------+------- 
      a | "foo" 
      b | "bar" 
(2 rows)

以文本形式返回结果，如下所示：

mydb=> SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}'); 
    key | value 
--------+------- 
      a | foo 
      b | bar 
(2 rows)

一个非常重要的函数为row_to_json（）函数，能够将行作为json对象返回，此函数常用来生成json测试数据，比如将一个普通表转换成json类型表，代码如下所示：

mydb=> SELECT * FROM test_copy WHERE id=1; id | name -------+------ 1 | a (1 row) mydb=> SELECT row_to_json(test_copy) FROM test_copy WHERE id=1; 
	row_to_json 
--------------------- 
{"id":1,"name":"a"} 
(1 row)

返回最外层的json对象中的键的集合，如下所示：

mydb=> SELECT * FROM json_object_keys('{"a":"foo", "b":"bar"}'); 
json_object_keys 
------------------ 
	ab 
(2 rows)

8.6 jsonb键/值的追加、删除、更新

jsonb键/值追加可通过“||”操作符，例如增加sex键/值，如下所示：

mydb=> SELECT '{"name":"francs","age":"31"}'::jsonb || '{"sex":"male"}'::jsonb; 
			?column? 
------------------------------------------------ 
{"age": "31", "sex": "male", "name": "francs"} 
(1 row)

jsonb键/值的删除有两种方法，一种是通过操作符“-”删除，另一种通过操作符“#-”删除指定键/值，通过操作符“-”删除键/值的代码如下所示：

mydb=> SELECT '{"name": "James", "email": "james@localhost"}'::jsonb - 'email'; 
 	?column? 
 ------------------- 
 {"name": "James"} 
 (1 row) 
 
 mydb=> SELECT '["red","green","blue"]'::jsonb - 0; 
 	?column?
------------------- 
["green", "blue"]

第二种方法是通过操作符“#-”删除指定键/值，通常用于有嵌套json数据删除的场景，如下代码删除嵌套contact中的fax键/ 值：

mydb=> SELECT '{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb #- '{contact,fax}'::text[]; 
					?column? 
--------------------------------------------------------- 
{"name": "James", "contact": {"phone": "01234 567890"}} 
(1 row)

删除嵌套aliases中的位置为1的键/值，如下所示：

mydb=> SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[]; 
				?column? 
-------------------------------------------------- 
{"name": "James", "aliases": ["Jamie", "J Man"]} 
(1 row)

键/值的更新也有两种方式，第一种方式为“||”操作符，“||”操作符可以连接json键，也可覆盖重复的键值，如下代码修改age键的值：

mydb=> SELECT '{"name":"francs","age":"31"}'::jsonb || '{"age":"32"}'::jsonb; 
 			?column? 
 --------------------------------- 
 {"age": "32", "name": "francs"} 
 (1 row)

第二种方式是通过jsonb_set函数，语法如下：
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) target指源jsonb数据，path指路径，new_value指更新后的键值，create_missing值为true表示如果键不存在则添加， create_missing值为false表示如果键不存在则不添加，示例如下：

mydb=> SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false); 
			jsonb_set 
--------------------------------- 
{"age": "32", "name": "francs"} 
(1 row) 

mydb=> SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,true); 
			jsonb_set 
------------------------------------------------ 
{"age": "31", "sex": "male", "name": "francs"} 
(1 row)

9、数据类型转换

前面几小节介绍了PostgreSQL常规数据类型和非常规数据类型，这一小节将介绍数据类型转换，PostgreSQL数据类型转换主要有三种方式：通过格式化函数、CAST函数、：：操作符
这里不做详细介绍。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：虚拟化的资源可以是虚拟化资源指一些可以

下一篇：javaerp源码 javaee项目源码

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯