使用可选的步长形参,来生成整数序列

select x from generate_series(1,51,13) as x;

字符和字符串lpad(要补齐的字符串,总长度,补上的字符串)

select lpad('ab',4,'0') as ab_lpad, rpad('ab',4,'0') as ab_rpad, lpad('abcde',4,'0') as ab_lpad_trunc;  

split_part(string text, delimiter text, column integer)

select split_part('abc.123.z45', '.', 2);    
select split_part('abc_123_z45', '_', 2);    

string_to_array(text, text)可以将一个字符串 拆分成为一个数组

select a[1] from string_to_array('abc_123_z45', '_') as a;

"unnest"(anyarray) string_to_array 和 unnest结合使用, 可以将字符串展开为若干记录

select a[1] from string_to_array('abc_123_z45', '_') as a;

正则表达式和模式匹配

\1 和 \2是模式匹配表达式中的元素. ( 是特殊字符 ( 的转义

select regexp_replace(
'6197306254',
'([0-9]{3})([0-9]{3})([0-9]{4})',
E'\(\\1) \\2-\\3') as x;`

将文本中的电话号码作为单独的行返回

select unnest(regexp_matches('Cell (619)852-5083. Casa 619-730-6254. Besame mucho.',
            E'[(]{0,1}[0-9]{3}[)-.]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}','g')
) as x; 

除了正则表达式专用的那些函数外,正则表达式还和 ~ 运算符 一起使用,如下:查出所有内嵌了电话号码的字符串

select description from mytable
where description ~ E'[(]{0,1}[0-9]{3}[)-.]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}';

5.3时间类型

最先进的,最全的时间类型

一个小时前

select current_timestamp - interval '1 hour';

提取当前小时

select extract(hour from current_timestamp);

获取当前时间戳

select current_timestamp; 

select now()::timestamp(0)without time zone;
select to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24');
select EXTRACT(HOUR FROM current_timestamp) - 1;

1. date

该类型仅存储 月,日,年,没有失去 , 小时 , 分 和 秒 的信息

2. time

该类型仅存储 小时,分,秒 信息, 不带日期 和 时区信息

3. timestamp

该数据类型存储了日期(年月日) 和 时间(时分秒)

4. timestamptz

存储了日期 和 时间 以及时区

5. timetz

携带了时区信息 没有日期信息

6. interval

该类型描述了时间段的长度,单位可以是小时,天, 月, 分钟, 或者 其他粒度.该类型适用于数学运算场景

7. tsrange

开区间闭区间限定符,定义了一个 从14:00 开始 到 15:00之前结束的一个时间段 timestamp
select '[2012-01-01 14:00 2012-01-01 15:00)'::tsrange;

8. tstzrange

定义 timestamptz 的开区间和闭区间

9. daterange

定义日期的开区间和闭区间
select '2016-05-26 3:43:00 PM'::timestamp - interval '1 hour'; select '2016-05-26 15:43:00'::timestamp - interval '1 hour';

重叠运算, 对时间戳和日期类型 使用 OVERLAPS overlaps 时间区域重叠运算符

select ('2012-10-25 10:00 AM'::timestamp ,'2012-10-25 2:00 PM'::timestamp) overlaps 
         ('2012-10-25 11:00 AM'::timestamp ,'2012-10-25 2:00 PM'::timestamp) as x,
         ('2012-10-25'::date ,'2012-10-26'::date) overlaps 
         ('2012-10-26'::date ,'2012-10-27'::date) as y;`  

generate_seriesinterval 作为步长生成时间序列数组

select (dt - interval '1 day')::date as eom
from generate_series('2012-02-01','2012-06-30',interval '1 month') as dt; 

date_part 或者 to_char 从日期类型和时间类型的数据值中抽取一部分

select dt, date_part('hour',dt) as mh,to_char(dt,'HH12:MI:AM') as tm
from generate_series('2012-03-11 12:30 AM','2012-03-11 3:00 AM',interval '15 minutes') as dt;  

generate_series 函数默认生成的是 timestamptz 类型数据,需要显示转换为timestamp类型

更多时间计算问题请见:https://www.cnblogs.com/mchina/archive/2013/04/15/3010418.html

5.4 数组类型

5.4.1 数组构造函数

array(子查询,或者 数组)

select array[2001,2002,2003] as yrs;
select array(
select distinct date_part('year',log_ts) from logs order_by date_part('year',log_ts)
);

可以直接把一个字符串格式书写的数组转化为真正的数组,语法如下

select '{Alex,Sonia}'::text[] as name, '{43,40}'::smallint[] as age;

string_to_array(text, text) 将一个用固定分隔符分隔的字符串转化为数组

select string_to_array('ca.ma.tx','.') as estados;

array_agg是一种变型聚合函数,它可以使用一组任何类型的数据并将其转换为数组

select array_agg(log_ts order by log_ts) as x from logs 
where log_ts between '2011-01-01'::timestamptz and '2011-01-15'::timestamptz;

5.4.2 引用数组中的元素

select a[1] from string_to_array('abc_123_z45_12s_12_we', '_') as a;

数组拆分与连接

第一个和第5个元素 a[1:5]

select a[1:5] from string_to_array('abc_123_z45_12s_12_we', '_') as a;   
select a[1:3] || a[4:5] from string_to_array('abc_123_z45_12s_12_we', '_') as a;

将数组元素展开为记录行 unnest

select unnest('{xox,oxo,xox}'::char(3)[]) as tic_tac_toe

你可以在一个 SELECT 语句中使用多个 unnest 函数,

但如果每个 unnest 展开后的记录行数不一致,或者说“对不齐”,

那么得到的最终结果将是这些结果集之间的笛卡儿积,看起来不太好理解。

SELECT
unnest('{three,blind,mice}'::text[]) As  t,
unnest('{1,2,3}'::smallint[]) As i;

如果你从上述一个数组中拿掉一个元素,

那么两个数组的元素就无法对齐了,此时展开得到的结果如示例 5-17 所示。

SELECT
unnest( '{blind,mouse}'::varchar[]) As v,
unnest('{1,2,3}'::smallint[]) As i;

示例 5-18:使用多实参 unnest 取消不平衡数组的嵌套

多实参 unnest 函数,只能在from 子句中出现

SELECT * FROM unnest('{blind,mouse}'::text[], '{1,2,3}'::int[]) As f(t,i);

5.5 区间类型

select int4range(1,5) = '[1,4]'::int4range;
select int4range(1,4) = '[1,4]'::int4range;
select int4range(1,5) @> '[1,4]'::int4range;
select 4 <@ '[1,4]'::int4range;

区间操作和集合操作

select '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp;
select '2011-01-10'::timestamp between '2011-01-01'::timestamp and '2011-01-10'::timestamp ;

离散区间

int4range、int8range

这是整数型离散区间,其定义符合前闭后开的规范化要求。

select '[-2,2]'::int4range; --[-2,3)
select 1 <@ '[-2,2]'::int4range; --t
select 1.2 <@ '[-2,2]'::int4range; --错误

--错误: 操作符不存在: numeric <@ int4range --没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.

numrange

-- 这是连续区间,可以用于描述小数、浮点数、或者双精度数字的区间。

select '[-2,2]'::numrange;--[-2,2]
select 1.2 <@ '[-2,2]'::numrange;--t

daterange

-- 这是不带时区信息的日期离散区间。

select '[2011-01-01,2011-03-01)'::daterange --[2011-01-01,2011-03-01)
SELECT daterange('2013-01-05','infinity','[]'); --[2013-01-05,infinity)

tsrange、tstzrange

这是时间戳(日期加时间)类型的连续区间,秒值部分支持小数。tsrange 不带时区信息,tstzrange 带时区信息。

5.5.4 定义含区间类型字段的表

-- 时间类型区间是很常用的,假设你有一张 employment 表,表中存储了公司聘请雇员的历史记录。 -- 你可以像示例 5-20 那样用时间区间来定义一个员工在公司的服务年限, -- 而不需要用起始时间和结束时间两个字段来表示。在本例中, -- 我们给 period 列添加了一个索引以使用我们的区间列加速查询。

CREATE TABLE employment (id serial PRIMARY KEY, employee varchar(20), period daterange);
CREATE INDEX idx_employment_period ON employment USING gist (period); 
INSERT INTO employment (employee, period)
VALUES ('Alex', '[2012-04-24, infinity)'::daterange), 
        ('Sonia', '[2011-04-24,2012-06-01)'::daterange), 
        ('Leo', '[2012-06-20, 2013-04-20)'::daterange), 
        ('Regina', '[2012-06-20, 2013-04-20)'::daterange);

5.5.5 适用于区间类型的运算符

-- 区间类型上用得最多的两个运算符是重叠运算符(&&)和包含运算符(@>)。 -- 要了解区间运算符的完整列表,请参考 PostgreSQL 官方手册中的“区间类型运算符”一节

1. 重叠运算符

-- 顾名思义,重叠运算符 && 的作用就是判定两个区间是否有重叠部分,如果有则返回 true,否则返回 false。

示例 5-21:查询谁与谁曾经同时在公司工作过

SELECT e1.employee, string_agg(DISTINCT e2.employee, ', ' ORDER BY e2.employee) As
colleagues
FROM employment As e1 INNER JOIN employment As e2
ON e1.period && e2.period
WHERE e1.employee <> e2.employee
GROUP BY e1.employee;

-- employee | colleagues
-- ---------+-------
-- Alex     | Leo, Regina, Sonia
-- Leo      | Alex, Regina
-- Regina   | Alex, Leo

-- Sonia | Alex

2. 包含与被包含关系运算符

SELECT employee FROM employment WHERE period @> CURRENT_DATE GROUP BY employee;
-- employee
-- ----
-- Alex

5.6 JSON数据类型

CREATE TABLE families_j (id serial PRIMARY KEY, profile json);

INSERT INTO families_j (profile) VALUES (
'{"name":"Gomez", "members":[
{"member":{"relation":"padre", "name":"Alex"}},
{"member":{"relation":"madre", "name":"Sonia"}},
{"member":{"relation":"hijo", "name":"Brandon"}},
{"member":{"relation":"hija", "name":"Azaleah"}}
]}');

json_extract_path、json_array_elements 以及 json_extract_path_text 这三个函数来读取表中所有家庭成员的信息。

SELECT json_extract_path_text(profile, 'name') As family,  
json_extract_path_text(
json_array_elements(
    json_extract_path(profile,'members')), 'member','name' ) As member
 FROM families_j;

select json_extract_path_text(json_array_elements(json_extract_path(profile,'members')),'member','name') from families_j;

运算符 ->> 和 #>> 是 json_extract_path_text 的简写。#>> 取用某个路径数组。

--示例 5-25 使用这些符号运算符对示例 5-24 进行了重写。

5.6.3 输出JSON数据

SELECT row_to_json(f) As x
FROM (SELECT id, profile->>'name' As name FROM families_j) As f;

5.8.1 所有表都有一个对应的自定义数据类型

5.8.2 构建自定义数据类型

-- 尽管仅仅通过建表就可以轻松创建复合数据类型, -- 但有时候我们仍会需要从头开始构建自己的数据类型。例如,使用以下语句可以构建一个复杂数字数据类型:

CREATE TYPE complex_number AS (r double precision, i double precision);
CREATE TABLE circuits (circuit_id serial PRIMARY KEY, ac_volt complex_number);
SELECT circuit_id, (ac_volt).* FROM circuits;
SELECT circuit_id, (ac_volt).r, (ac_volt).i FROM circuits;

5.8.3 为自定义数据类型构建运算符和函数

在构建自定义数据类型后,你自然就会需要为其创建相应的函数和运算符。 我们接下来将演示如何为 complex_number 类型创建一个 + 运算符, 而创建处理函数的方法将放在本书后面的第 8 章中进行介绍。我们在前面已经介绍过, 每个运算符都有一个底层实现函数,该函数需要一个或者两个实参,运算符就是这个函数的符号化别名。 在 PostgreSQL 官方手册的“创建运算符”这一节 (http://www.postgresql.org/docs/current/interactive/sql-createoperator.html) 中你可以看到系统允许使用哪些字符来定义新的运算符。

运算符不仅仅是其底层实现函数的别名,它还可以提供一些可以帮助规划器更好工作的优化信息, 规划器借助这些信息可以判定如何使用索引,如何以最低的成本访问数据,以及哪些运算符表达式是等价的。 这些信息的完整列表以及每一类信息的具体作用可以参考官方手册中“运算符的优化信息”这一节的内容 (http://www.postgresql.org/docs/current/interactive/xoper-optimization.html)。

创建运算符的第一步是创建其底层实现函数,如示例 5-34 所示。

示例 5-34:为 complex_number 创建底层实现函数

 CREATE OR REPLACE FUNCTION add(complex_number, complex_number) RETURNS complex_number AS
 $$
  SELECT ( (COALESCE(($1).r,0) + COALESCE(($2).r,0)),
     (COALESCE(($1).i,0) + COALESCE(($2).i,0)) )::complex_number;
 $$
 language  sql;  

接下来要创建一个运算符来代表此函数,如示例 5-35 所示。

示例 5-35:为 complex_number 类型定义 + 运算符

CREATE OPERATOR +(
    PROCEDURE = add,
    LEFTARG = complex_number,
    RIGHTARG = complex_number,
COMMUTATOR = +);

然后我们测试一下这个新的 + 运算符:

SELECT (1,2)::complex_number + (3,-10)::complex_number;
-- 输出结果是 (4,-8)。
-- 虽然我们在此处没有举例说明,但你可以对函数和运算符进行重载,
-- 以使其可以接受多种不同类型的输入。例如,你可以创建一个支持 complex_number
-- 和 integer 相加的 add 函数和相应的 + 计算符,这就实现了对原逻辑的扩展。
-- 支持自定义数据类型和运算符让 PostgreSQL 从机制上具有了自我演进的能力,
-- 开源社区无数开发人员利用此能力为 PostgreSQL 平台添砖加瓦,
-- 随着这个开发平台的羽翼日渐丰满,我们离“一切皆以表驱动”的理想境界也越来越近。

select dt
from generate_series('2016-10-20 15:00:00','2016-11-02 10:00:00',interval '1 hour') as dt;