PostgreSQL各数据类型的内置函数

参考《PostgreSQL实战》

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

PostgreSQL 支持数字类型操作符和丰富的数学函数

例如支持加、减、乘、除、模取取余操作符

SELECT 1+2, 2*3, 4/2, 8%3;

按模取余

SELECT mod(8,3);
结果：2

四舍五入函数：

SELECT round(10.4) , round(10.5);
结果：10, 11

返回大于或等于给出参数的最小整数

SELECT ceil(3.6) , ceil(-3.6);
结果：4, -3

返回小于或等于给出参数的最小整数

floor(3.6) 结果：3

3.2.2 字符类型函数

PostgreSQL 支持丰富 的字符函数， 下面举例说明

计算字符串中的字符数

select char_length("abcd");
结果: 4

计算字符串占用的字节数

select octet_length("abcd");
结果: 4

指定字符在字符串中的位置(首次出现)

select position("a" in "abcda")
结果: 1
select position("x" in "abcda")
结果: 0

提取字符串中的子串

select substring("hello" from 3 for 4)
结果: llo
select substring("hello" from 4 for 1)
结果: l

拆分字符串

split_part (string text, delimiter text, field int )

根据 delimiter 分隔符拆分字符串 string,并返回指定字段,字段从 1 开始

select split_part("abc@def1@nb", "@", 1)
结果: abc

3.3.2 时间/日期类型操作符

时间 、 日期数据类型支持的操作符有加、减、乘、 除，

日期相加

select date "2017-07-29" + interval"1 days"
结果: 2017-07-30 00:00:00

日期相减

select date "2017-07-29" - interval"1 hour"
结果: 2017-07-28 23:00:00

日期相乘

select 100* interval "1 second"
结果: 00:01:40

日期相除

select interval "1 hour" / double precision "3"
结果: 00:20:00

3.3.3 时间／日期类型常用函数

显示当前时间

select current_date, current_time;
结果: 2019-11-23, 20:20:55.635115+08

另一个非常重要的函数为 EXTRACT 函数，可以从日期 、 时间数据类型中抽取年、月、日、 时、分、秒信息

EXTRACT(field FROM source)

field 值可以为 century、year、 month、 day、 hour 、 minute、 second等， source类型为
timestamp、 time、 interval的值的表达式。

取年份

select extract ( year from now())
结果: 2019

月份&月份里的第几天(当前时间2019-11-24)

select extract (month from now()), extract(day from now());
结果: 11, 24

抽取小时、分钟、秒语法同上，单词为hour,minute,second

抽取当前日期是所在年份的第几周&多少天

select extract(week from now()), extract(day from now())
结果: 47, 24

3.5.3 网络地址函数

PostgreSQL 网络地址类型支持一系列内置函数，下面举例说明。

取IP地址 ， 返回文本格式。

select host(cidr "192.168.1.0/24");
结果: 192.168.1.0

取IP地址和网络掩码，返回文本格式

select text(cidr "192.168.1.0/24");
结果: 192.168.1.0/24

取网络地址子网掩码，返回文本格式

select netmask(cidr "192.168.1.0/24");
结果: 255.255.255.0

3.6.4 数据元素的追加、删除、更新

数组元素的追加使用array_append 函数

array_append(anyarray, anyelement)

array_append 函数向数组末端追加一个元素

select array_append(array[1,2,3], 4);
结果: {1,2,3,4}

数据元素追加到数组也可以使用操作符 ||

select array[1,2,3] || 4;
结果: {1,2,3,4}

数组元素的删除使用 array_remove 函数

array_remove(anyarray, anyelement)

array_remove 函数将移除数组中值等于给定值的所有数组元素

select array[1,2,2,3], array_remove(array[1,2,2,3], 2);
结果: {1,2,2,3}, {1,3}

数组元素修改:

update test_array_table set array_i[3] = 4 where id = 1;
UPDATE 1

更新整个数组

update test_array_table set array_i=array[7,8,9] where id = 1;
UPDATE 1

3.6.5 数组操作符

3.6.6 数组函数

数组维度

select array_ndims(array[1,2]);
结果: 1

数组长度(获取指定维度的长度)

select array_length(array[1,2], 1);
结果: 2

select array_length(array[[1],[2]], 2);
结果: 1
select array_length(array[[1],[2]], 1);
结果: 2

返回数组中某个数组元素第一次出现的位置

select array_position(array["a", "b", "c", "d"], "d");
结果: 4

数组元素替换可使用函数 array_replace

array replace(anyarray, anyelement, anyelement)

函数返回值类型为 anyarray，使用第二个 anyelement 替换数组中的相同数组元素

select array_replace( array[1,2,3,3,4], 3, 6);
结果: {1,2,6,6,4}

将数组元素输出到字符串，可以使用 array_to_string 函数

array_to_striiing( anyarray, text [, test])

函数返回值类型为text ，第一个text 参数指分隔符，第二个 text 表示将值为 NULL 的
元素使用这个字符串替换

select array_to_string(array[1,2,null,3], ",", "-1");
结果: 1,2,-1,3

还有array_to_json(array)函数，将数组直接转换为json

3.7.4 范围类型函数

以下列举范围类型常用函数

取范围下界

select lower(int4range(1,10));
结果: 1

取范围上界

select upper(int4range(1,10));
结果: 10

范围是否为空

select isempty(int4range(1,10));
结果: f
select isempty(int4range(10,10));
结果: t

3.8.5 jsonb与json函数

json 与 jsonb 相关的函数非常丰富， 下面举例说明

扩展最外层的 json 对象成为一组键／值结果集

select * from json_each("{"a":"foo", "b":"bar"}");
结果:
a | "foo"
b | "bar"

文本形式返回结果

select * from json_each_text('{"a":"foo", "b":"bar"}');
结果：
a | foo
b | bar

row_tojson()函数，能够将行作为json对象返回。
此函数常用来生成 json 测试数据，比如将一个普通表转换成 json 类型表

select * from test_copy where id = 1;

id | name
---+-----
1  | a

select row_to_json(test_copy) from test_copy where id = 1;

row_to_json
--------------
{"id":1,"name":"a"}

返回最外层的 json 对象中的键的集合

select * from json_object_keys('{"a":"foo", "b":"bar"}');

json_object_keys
---------------
a
b

3.8.6 jsonb 键／值的追加、删除、更新

jsonb 键／值追加可通过“||”操作符

select '{"name":"francs","age":"31"}' :: jsonb || '{"sex":"male"}' :: jsonb;

{"age": "31", "sex": "male", "name": "francs"}

jsonb 键 ／值的删除有两种方法，一种是通过操作符
“-”删除，另一种通过操作符“#_”删除指定键／值

第一种

SELECT '{"name" : "James", "email": "james@localhost"}':: jsonb - 'email';
{"name": "James"}

select '["red","green","blue"]'::jsonb - 0;
["green", "blue"]

第二种 操作符“#_”删除指定键／值，通常用于有嵌套 json 数据删除的场
景

• 删除嵌套contact中的fax 键值

SELECT
'{"name":"James","contant":{"phone":"01234567890", "fax":"01923 342234"}}' :: jsonb 
#-
'{contant,fax}' :: TEXT []

结果：
{"name": "James", "contant": {"phone": "01234567890"}}

• 删除嵌套contant中，坐标为0的键值

SELECT
'{"name":"James","contant":["first", "second", "third"]}' :: jsonb 
#-
'{contant,0}' :: TEXT []

结果: 
{"name": "James", "contant": ["second", "third"]}

键／值的更新也有两种方式

• 第一种方式为“||”操作符，“||”操作符可以连接 json键，也可以覆盖重复的键值

select '{"name":"AganRun", "age":"31"}'::jsonb || '{"age":"100"}' :: jsonb;

{"age": "100", "name": "AganRun"}

• 第二种方式是通过jsonb_set函数
  jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

target 指源jsonb数据， path指路径， new_value 指更新后的键值， creat_missing 值为
true 表示如果键不存在则添加, create_missing 值为 false 表示如果键不存在则不添加

SELECT jsonb_set ('{"name":"francs","age":"31"}':: jsonb ,'{age}','"32"'::jsonb, false);

{"age": "32", "name": "francs"}

SELECT jsonb_set  ('{"name":"francs","age":"31"}':: jsonb ,'{sex }','"male"':: jsonb, true);

{"age": "31", "sex": "male", "name": "francs"}

数据类型转换

格式化函数、CAST函数、操作符

3.9.1 通过格式化函数进行转换

3.9.2 通过 CAST 函数进行转换

varchar 字符类型转换成 text 类型

select cast(varchar'123' as test);
 
123

varchar 字符类型转换成 int4 类型

select cast(varchar'123' as text);

123

3.9.3 通过::操作符进行转换

例子转换成 int4 或 numeric 类型

select 1::int4, 3/2::numeric;

1 , 1.5

三种数据类型转换方法，第－种方法兼容性相对较好，第三种方法用法简捷