PG系列4-PostgreSQL的数据类型

最新推荐文章于 2024-05-29 11:12:11 发布

玻璃酸钠滴眼液

最新推荐文章于 2024-05-29 11:12:11 发布

阅读量7.5k

点赞数 5

分类专栏： PostgreSQL 文章标签： postgresql sql json

本文链接：https://blog.csdn.net/weixin_41191813/article/details/118522836

版权

PostgreSQL 专栏收录该内容

8 篇文章 4 订阅

订阅专栏

文章目录

前言
1、数字类型
- 1.1 数字类型列表
- 1.2 数字类型操作符和数学函数
2、字符类型
- 2.1 字符类型表
- 2.2 字符类型函数
3、时间/日期类型
4、布尔类型
5、网络地址类型
6、数组类型
7、范围类型
8、json/jsonb类型
9、数据类型转换

前言

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

1、数字类型

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

1.1 数字类型列表

PG支持的数字类型如表：

在这里插入图片描述
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 字符类型表

在这里插入图片描述
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 时间/日期类型列表

在这里插入图片描述
我们通过一个简单的例子理解这几个时间、日期数据类型，先来看看系统自带的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支持的布尔类型如表

在这里插入图片描述
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 网络地址类型列表

在这里插入图片描述
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支持丰富的网络地址数据类型操作符，如表:
在这里插入图片描述

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数组元素支持丰富操作符，如表所示。
在这里插入图片描述

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函数、：：操作符
这里不做详细介绍。

玻璃酸钠滴眼液

关注

5
点赞
踩
24

收藏

觉得还不错? 一键收藏
0
评论
PG系列4-PostgreSQL的数据类型

文章目录1、数字类型2、字符类型3、时间/日期类型4、布尔类型5、网络地址类型6、数组类型7、范围类型8、json/jsonb类型9、数据类型转换1、数字类型2、字符类型3、时间/日期类型4、布尔类型5、网络地址类型6、数组类型7、范围类型8、json/jsonb类型9、数据类型转换...
复制链接

扫一扫