在实际应用中, 对于具体的数据计算我们会找相应的函数来实现. 而计算需求不同的表达, 往往会使得我们使用不同的函数或方式来实现. 或者也可以说, 同一计算可以使用多种不同的表达方式实现.
PostgreSQL 提供非常丰富的类型及函数, 一些常用的函数可参考此文.
以下我们通过实例来展示几个常用的函数表达场景
首先, 我们创建示例数据如下
- CREATE TABLE table_test (
- id int PRIMARY KEY,
test_name varchar(10), -- 名称
group_code varchar(10), -- 组号
create_at timestamp, -- 创建时间
status bool, -- 状态
test_desc varchar(100) -- 描述
);
-- 插入以下示例数据
- INSERT INTO table_test (id, test_name, group_code, create_at, status, test_desc)
- VALUES
- (1, '一号', '01', now(), true, '评为 A'),
- (2, '一号', '01', now(), true, '评为 A'),
- (3, '二号', '02', now(), true, '评为 B'),
- (4, '三号', '01', now(), null, '评为 AC'),
- (5, '四号', '03', now(), true, '评为 C'),
- (6, '五号', '03', now(), true, '评为 AB');
NULL 相关
当查询条件中相关字段存在 NULL 值时, 如果直接使用 WHERE 字段 = @参数值, 则怎么都匹配不了为 NULL 值的记录, 因为 NULL = 任何值, 包括 NULL=NULL 本身都不会返回 true, 只会仍然是 NULL.
此时, 可以将 NULL 进行转换, 当字段值为 NULL 时, 转换为一个默认值.
使用 coalesce() 可以表达此功能, 它的功能是返回参数列表 (支持不限定数量的参数) 中, 第一个非空值.
那如果我们需要根据 status (包含 NULL 值, NULL 时默认 false)来查询, 参数值可能是 true/false, 则可以表达为 coalesce(status, false), 当 status 为 NULL 时, 自然返回的就是后边的 false.
此时: coalesce(status, false) = (case when status is null then false else status end)
-- status 中存在 NULL 值, 以下参数可能为 true/false
- SELECT * FROM table_test
- WHERE coalesce(status, false) = ?;
- SELECT * FROM table_test
- WHERE (case when status is null then false else status end) = ?;
时间相关
(1)当前时间及时间格式化输出
函数 now() 取得当前系统时间, 等同于系统变量 CURRENT_TIMESTAMP
使用 to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS US') 可以格式化时间, 格式符含义如下:
YYYY 年, MM 月, DD 日, HH24 二十四小时制的时, MI 分, SS 秒, US 微秒
SELECT now(), CURRENT_TIMESTAMP, to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS US')
-- 输入结果
- "2019-04-21 23:24:45.276729+08"
- "2019-04-21 23:24:45.276729+08"
- "2019-04-21 23:24:45 276729"
(2)时间差
两个时间相差的秒数使用:
extract(epoch FROM ( 时间 1 - 时间 2 ))
通过该函数, 我们可以制作一个用于表示时间已过去多久的函数.
- CREATE OR REPLACE FUNCTION public.f_time_span(
- t timestamp without time zone)
- RETURNS character varying
- LANGUAGE 'plpgsql'
- AS $$
- DECLARE
- lv_span_double double precision;
- lv_span int8;
- BEGIN
-- 取得当前时间与参数时间 相差的秒数
SELECT extract(epoch FROM (now() - t )) into lv_span_double;
-- 转换为整型
- lv_span := cast(lv_span_double as int8);
- IF lv_span < 11 THEN
- RETURN '几秒前';
- END IF;
- IF lv_span < 60 then
- RETURN lv_span || '秒前';
- END IF;
- IF lv_span < 3600 THEN
- RETURN (lv_span / 60) || '分钟前';
- END IF;
- IF lv_span < 86400 THEN
- RETURN (lv_span / 3600) || '小时前';
- END IF;
- IF lv_span < 30 * 86400 THEN
- RETURN (lv_span / 86400) || '天前';
- END IF;
- IF lv_span < 365 * 86400 THEN
- RETURN (lv_span / (30*86400)) || '月前';
- END IF;
- RETURN (lv_span / (365*86400)) || '年前';
- END
- $$;
我们应用于查询中, 如作为创建时间隔现在多久的一个描述
SELECT test_name, create_at, f_time_span(create_at) span_desc FROM table_test
image
UUID 相关
使用 UUID, 需要安装扩展 uuid-ossp, 扩展安装成功以后, 就可以通过 uuid_generate_v4() 或 uuid_generate_v1() 取得 UUID.
- CREATE EXTENSION "uuid-ossp";
- SELECT uuid_generate_v4()
-- 输出 "da28ce8a-ca9b-483f-918e-dce96fe7137f"
聚合相关
PostgreSQL 提供了除 SUM, COUNT, MAX, MIN, AVG 等之外更多的便捷的聚合函数.
string_agg(表达式, '分隔符') -- 按聚合的表达式值用分隔符连接成一串文本
array_agg(表达式) -- 将聚合的表达式值形成一个数组
如示例数据中, 我们希望返回每一个组号内的名称列表, 则可以:
- SELECT GROUP_CODE,
- string_agg(test_name, ','),
- array_agg(test_name)
- FROM table_test GROUP BY GROUP_CODE
得到结果如下图
其它补充
(1)MD5 加密
在用户信息表中, 密码信息通常不明文存储, 有一种方式是使用密码的 MD5 进行单向加密存储从而保密性更强, PostgreSQL 想人之所想, 提供了直接的 md5 函数, md5(text) 直接得到 32 位的 MD5 加密结果.
(2)正则相关
PostgreSQL 支持正则表达式, 这为查询提供了极大的灵活性与表达空间.
语法格式为:'字符串' ~ '正则表达式', 含义类似 IsMatch('字符串' ,'正则表达式')
~* 则表示忽略大小写,!~ 或 !~* 表示否定
也可以使用 not '字符串' ~ '正则表达式' 表示否定
-- test_desc 包含 B 或 C 的
SELECT * FROM table_test WHERE test_desc ~ 'B|C';
-- test_desc 包含 ABC 中两个字母相邻的
SELECT * FROM table_test WHERE test_desc ~ '[A-C]{2}';
-- test_desc A 结尾的
SELECT * FROM table_test WHERE test_desc ~ 'A$';
-- test_desc 不是 A 结尾的
SELECT * FROM table_test WHERE test_desc !~ 'A$';
PostgreSQL 务实应用 (一 / 5) 树形层级
PostgreSQL 务实应用 (二 / 5) 插入冲突
PostgreSQL 务实应用 (三 / 5) 分表复制
PostgreSQL 务实应用(四 / 5)JSON
来源: http://www.jianshu.com/p/3fc6f4fe8105