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

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

巴特西