PostgreSQL的特色sql语法

1、视图

简言之，视图就是持久化存储在数据库中的一个查询语句。

1.1、单表视图

创建单表的视图：

CREATE or REPLACE VIEW census.vw_facts_2019 as SELECT fact_type_id,val,yr FROM census.facts WHERE yr=2011;

下面的删除命令将仅删除yr=2011的记录：

delete from census.vw_facts_2019 where val=0;

以下update操作更新不了任何记录：

update census.vw_facts_2019 set val=1 where val=0 and yr=2012;

2、postgresql专有sql语法

2.1、distinct on

其功能类似distinct但且可以精确到更细的粒度。distinct会将结果集中完全重复的记录剔除，但distinct on 可以将结果集中指定字段值的重复记录剔除。

示例：

SELECT DISTINCT ON (left(tract_id, 5)) left(tract_id, 5) AS county, tract_id, tract_name from census.lu_tracts ORDER BY county, tract_id;

同时查询语句中order by子句的排序字段列表的最左侧必须是distinct on指定的字段列表，既保证整个结果集是按照这几个字段排序的，这样最终去重后得到的结果才是你想要的。

2.2、limit和offset关键字

limit关键字指定了查询时仅返回指定数量的记录，offset关键字指定了从第几条记录开始返回。一般这两个关键字和order by联用。

示例：要求查询结果集仅返回从第3条开始的3条记录

SELECT DISTINCT ON (left(tract_id, 5)) left(tract_id, 5) AS county, tract_id, tract_name from census.lu_tracts ORDER BY county, tract_id LIMIT 3 OFFSET 2;

2.3、简化的类型转换语法

ANSI SQL标准中定义了一个名为CAST的类型转换函数，可以实现数据类型之间的互相转换。例如CAST('2011-1-11' as date)可以将文本转换为一个日期类型。而postgresql支持一种简写语法，该语法使用了两个冒号来表示转换关系，具体格式为：'2011-1-11'::date。还可以级联执行多个类型转换动作，例如：someXML::text::integer。

2.4、delete using语法

using子句可以将需要借助的一个或者多个中间表（或者子查询）纳入同一个delete语句中。在下列示例中我们借助一个关联查询实现了删除census.facts表中符合short_name='s01'这个条件的记录

DELETE from census.facts USING census.lu_fact_types as ft WHERE fact.fact_type_id = ft.fact_type_id AND ft.short_name='s01';

2.5、在查询中使用复合数据类型

联用array_agg和array_to_json这两个函数来将语句的查询结果转换为一个json对象后输出，示例：

SELECT array_to_json(array_agg(f)) AS cat FROM (select name,ggstart_time,href,min(ctid) from gg group by name,ggstart_time,href having count(href) >1 order by ggstart_time desc) as f;

postgresql 9.3版提供了一名为json_agg的函数，该函数的效果相当于上面示例中array_agg和array_to_json联用的效果，但json_agg执行速度更快，示例：

SELECT json_agg(f) AS cats FROM (SELECT MAX(fact_type_id) AS max_type,category FROM census.lu_fact_types
GROUP BY category) AS f;

2.6、适用于聚合操作的FILTER子句

SELECT student,AVG(score) FILTER (WHERE subject='algebra') AS algebra,  AVG(score) FILTER (WHERE subject='physics') AS physics from test_scores GROUP BY student;

对于求平均值、求合计值以及其他很多聚合函数来说，case和FILTER子句是等价的，即二者可以起到相同的作用。FILTER子句的优势在于写法比较清晰简洁并且操作大数据量时速度比较快。

示例：FILTER子句与array_agg函数的配合使用

SELECT student,array_agg(score) FILTER (WHERE subject='algebra') AS algebra,  array_agg(score) FILTER (WHERE subject='physics') AS physics from test_scores GROUP BY student;

这样的输出是不会出现null值。

2.7、CTE表达式

2.7.1、基本CTE用法介绍

基本CTE的用法如下示例。with关键字后跟着的就是CTE表达式。

with cte AS (SELECT name, substring(name,1,5) as county_code, COUNT(*) OVER(PARTITION BY substring(name,1,5)) as cnt_tracts from gg)   SELECT max(name) as last_tract,county_code,cnt_tracts FROM cte WHERE cnt_tracts > 10 GROUP BY county_code,cnt_tracts；

CTE表达式的名称是cte，其本体是由一个select语句定义出来的，查询字段列表中包含name，county_code，cnt_tracts三个列。外围SQL语句会将CTE作为一个临时表来使用。

2.7.2、可写CTE用法介绍

它扩展了CTE的功能范畴，从只读扩展为可写。下面示例来演示此功能，首先创建一个子表：

CREATE TABLE logs_2011_01_02(PRIMARY KEY (log_id), CONSTRAINT chk CHECK (log_ts >= '2011-01-01' AND log_ts < '2011-03-01')) INHERITS (logs_2011);

要指定命名约束，使用关键字CONSTRAINT，后跟标识符以及check约束定义。

使用可写CTE将数据从一个分支移动到另一个分支

WITH t as (DELETE FROM ONLY logs_2011 WHERE log_ts < '2011-03-01' RETURNING *) INSERT INTO logs_2011_01_02 SELECT * from t;

2.8、lateral横向关联语法

示例：

select * from census.facts L inner join LATERAL (select * from census.lu_fact_types where category=CASE WHEN L.yr=2011 THEN 'Housing' ELSE category END) R ON L.fact_type_id=R.fact_type_id;

通过使用LATERAL语法可以在一个from子句中跨两个表共享多列中的数据。但有个限制就是仅支持单向共享，即右侧的表可以提取左侧表中的数据，但反过来不行。