postgresql入门详细总结

发表于 | 分类于 | 阅读次数:
字数统计: 2,866 字 | 阅读时长 ≈ 13 分钟

本文介绍postgresql的部分基本数据类型和基本操作,包括时间类型,字符串操作,数组操作等

postgresql下载地址:https://www.postgresql.org/download/

一、字符串操作

lpad

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

1
2
3
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

postgresql分隔字符串,split_part
语法:split_part(string text, delimiter text, column integer) 

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

string_to_array(text, text)

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

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

将字符串展开为若干记录

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

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

二、postgresql正则表达式和模式匹配

模式匹配

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

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

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

1
2
3
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;

~ 运算符

除了正则表达式专用的那些函数外,正则表达式还和 ~ 运算符 一起使用,

如下:查出所有内嵌了电话号码的字符串 

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

三、postgresql时间类型

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

时间加减法：一个小时前 

1
select current_timestamp - interval '1 hour';

提取当前小时

1
select extract(hour from current_timestamp);

获取当前时间戳

1
2
3
4
5
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');
#获取部分时：hour
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 

1
select '[2012-01-01 14:00 2012-01-01 15:00)'::tsrange;

8. tstzrange

定义 timestamptz 的开区间和闭区间

9. daterange

定义日期的开区间和闭区间 

1
2
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 时间区域重叠运算符 

1
2
3
4
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;`

生成整数序列

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

1
2
3
4
5
6
7
8
9
select x from generate_series(1,51,13) as x;
``` 
## `generate_series`  
`generate_series` 用 `interval` 作为步长生成时间序列数组

```sql
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 从日期类型和时间类型的数据值中抽取一部分 

1
2
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类型

更多时间计算问题请见:PostgreSQL的时间/日期函数使用

四 数组类型

1.数组构造函数

array(子查询,或者 数组) 

1
2
3
4
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)
);

字符串数组转化为数组

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

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

string_to_array(text, text)

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

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

array_agg

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

1
2
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;

2.引用数组中的元素

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

3.数组拆分与连接

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

1
2
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

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

你可以在一个 SELECT 语句中使用多个 unnest 函数，
但如果每个 unnest 展开后的记录行数不一致，或者说“对不齐”，
那么得到的最终结果将是这些结果集之间的笛卡儿积，看起来不太好理解。

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

如果你从上述一个数组中拿掉一个元素，那么两个数组的元素就无法对齐了，此时展开得到的结果如示例下

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

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

使用多实参 unnest 取消不平衡数组的嵌套

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

五、区间类型

1
2
3
4
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;

区间操作和集合操作

1
2
3
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: 这是整数型离散区间，其定义符合前闭后开的规范化要求。

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

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

numrange

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

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

daterange

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

1
2
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 带时区信息。

定义含区间类型字段的表

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

1
2
3
4
5
6
7
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);

适用于区间类型的运算符

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

1. 重叠运算符

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

示例 ：查询谁与谁曾经同时在公司工作过

1
2
3
4
5
6
7
8
9
10
11
12
13
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. 包含与被包含关系运算符

1
2
3
4
SELECT employee FROM employment WHERE period @> CURRENT_DATE GROUP BY employee;
-- employee
-- ----
-- Alex

六、JSON数据类型

JSON类型

形如：{key:value}

1
2
3
4
5
6
7
8
9
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
这三个函数来读取表中所有家庭成员的信息。

1
2
3
4
5
6
7
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 的简写。#>> 取用某个路径数组。

示例使用这些符号运算符对上面进行了重写。

输出JSON数据

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

七、构建自定义数据类型

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

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

1
2
3
4
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;

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

创建运算符的第一步是创建其底层实现函数
示例 ：为 complex_number 创建底层实现函数

1
2
3
4
5
6
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;

接下来要创建一个运算符来代表此函数
示例：为 complex_number 类型定义 + 运算符

1
2
3
4
5
6
7
8
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 平台添砖加瓦，
  • 随着这个开发平台的羽翼日渐丰满，我们离“一切皆以表驱动”的理想境界也越来越近。

在 PostgreSQL 官方手册的“创建运算符”这一节中你可以看到系统允许使用哪些字符来定义新的运算符。
参考官方手册中“运算符的优化信息”

-------------感谢您的阅读祝您生活愉快!-------------

本文标题:postgresql入门详细总结

文章作者:小明

发布时间:2018年01月19日 - 16:01

最后更新:2018年01月19日 - 16:01

原始链接:https://www.iteway.com/2018/01/19/postgresql入门详细总结/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

支持一杯咖啡
0%