SQL语言当今在数据查询分析这块地位至今无法撼动，曾经的NoSQL也开始疲软，口号从”no SQL”也变成了“not only SQL”或“no , SQL！”， 但SQL的开发能力参差不齐，有些是从ORACLE数据库转到postgreSQL的，相同SQL的结果不并相同，在性能上也并不是所有人都可以编写高效正确查询，这里简单列几个在PG中几个SQL注意事项。
1，除以整数得整数
# oracle
select 1/3 value from dual;
value
-----------------------------------------
.3333333333333333333333333333333333333333
# PostgreSQL
select 1/3 as value ;
value
----------
0
在postgresql中如果想要得到除法预期结果，需要把其中一个转换为浮点数
select 1/3::float as value
value
-------------------
0.3333333333333333
2, 除数为0
在其它数据库中也一样，除数据不可以为0
select 2/0 as value;
ERROR: division by zero
分母不可以为0这是一个算数要求，在写SQL时需要避免，可以当分母为0时返回null(如果第一个参数等于第二个参数，则函数 NULLIF 返回 null。)或-1(COALESCE该函数返回其第一个不为 null 的参数)等特定值,同样适用于oracle(nvl函数).
SELECT 1 / NULLIF(0, 0) as value;
value
--------------------
null
SELECT COALESCE(1 / NULLIF(0, 0), -1) as value;
value
---------
-1
3, union与union all
union相比union all增加了排除重复行，如果不需要排除重复行，出于性能考虑最好使用union all
explain analyze
select padding from t1
union
select padding from t2;
HashAggregate (cost=754.74..968.10 rows=21336 width=32) (actual time=9.343..10.560 rows=10000 loops=1)
Group Key: t1.padding
Batches: 1 Memory Usage: 1809kB
-> Append (cost=0.00..701.40 rows=21336 width=32) (actual time=0.017..4.587 rows=20000 loops=1)
-> Seq Scan on t1 (cost=0.00..190.68 rows=10668 width=32) (actual time=0.016..1.576 rows=10000 loops=1)
-> Seq Scan on t2 (cost=0.00..190.68 rows=10668 width=32) (actual time=0.011..1.488 rows=10000 loops=1)
Planning Time: 0.147 ms
Execution Time: 11.632 ms
explain analyze
select padding from t1
union all
select padding from t2;
Append (cost=0.00..488.04 rows=21336 width=32) (actual time=0.018..4.418 rows=20000 loops=1)
-> Seq Scan on t1 (cost=0.00..190.68 rows=10668 width=32) (actual time=0.017..1.502 rows=10000 loops=1)
-> Seq Scan on t2 (cost=0.00..190.68 rows=10668 width=32) (actual time=0.011..1.469 rows=10000 loops=1)
Planning Time: 0.144 ms
Execution Time: 5.131 ms
note:
在有些时时候union 执行计划可能不是HashAggregate 而是sort unique.
4, count可为null列
使用聚合函数对可为null的列运算时需要注意,如count(col)
create table t1 (id int, padding text);
insert into t1 (id, padding)
select id, md5(random()::text)
from generate_series(1, 10) as id
order by random();
insert into t1 values(1,null);
select count(padding) cntp,count(*) cnt from t1;
cntp cnt
----- -----
10 11
note:
Null在统计行记录数时会忽略.
5， 日期to_date 不带时分秒
oracle的to_date函数比postgresql的多时分秒，我们一个项目中从oracle迁移过来的， 每隔5分钟一个批次插入数，同时以时间列判断这一批是否已存在。迁移到postgresql后发现入库的数据总比oracle少，但程序无报错，后分析每天只入了第一批，因为postgresql的to_date只精确到天。之前我的blog记录过<Oracle迁移到PostgreSQL注意事项: to_date>
select to_date('2023-1-1 12:13:14','yyyy-mm-dd hh24:mi:ss')
to_date
----------
2023-01-01
可以写成
select to_timestamp('2023-1-1 12:13:14','yyyy-mm-dd hh24:mi:ss')
或配置orafce重载to_date。
6， 时区
时区问题有可能在时间截断为天时，跨了天产生错误数据，尤其是全球化业务， 如存储created_at的是timestamp，转换为date统计每天的数据时.
如
SELECT created_at::date, COUNT(*)
FROM sale
GROUP BY 1;
SHOW timezone;
TimeZone
-------
GB
select (now()+ interval '15 H')::date;
date
----------
2023-05-31
SET TIME ZONE 'Asia/Shanghai'
select (now()+ interval '15 H')::date;
date
-----------
2023-06-01
统计转date时可以带上timezone,如
SELECT (now() at time zone 'Asia/Shanghai')::date;
Note：
要在 PostgreSQL 中获取时区名称的完整列表，请查询视图pg_timezone_names。
7， between 范围
between 的时间过滤是包含性的，等同于>= and <=, 有时你可能只想统计某月份的。
select * from xx
where created between '2023-5-1' and '2023-6-1';
这是错误的，等效于
select * from xx
where created>='2023-5-1' and created<='2023-6-1'; 而如果你只要202305月份应该是 select * from xx where created>='2023-5-1' and created<'2023-6-1';
-- or --
select * from xx
where created between '2023-5-1' and '2023-5-31';
8， 索引列转换
这一点和oracle一样，不建议在已创建的索引列上使用转换或函数，容易导致索引无法使用。
如
where id::float < 5;
虽然id列有索引也无法使用，改为右侧转换如:
where id < 5.0::int;
9， WITH子名
在 PostgreSQL 12 之前的版本中，当 with使用不当时，它们可能会导致内存使用量增加和性能下降, with 会创建一个临时结构，如果你是个oracle DBA，它就像是 with 加了/*+materialize*/ 物化的hint，在多次引用时可以避免冗余多次评估，听上去很不错，但是它像硬币的的另一面可能不能view展开，主查询的谓词无法推进到with中。 但是从postgresql 12改变处理方式.
create table foo (id int, padding text);
insert into foo (id, padding)
select id, md5(random()::text)
from generate_series(1, 1000000) as id
order by random();
create index foo_id_ix on foo (id);
explain (analyze on, timing on)
with cte as (
select * from foo
)
select * from cte where id = 500000;
# posgresql 11
QUERY PLAN
CTE Scan on cte (cost=18334.00..40834.00 rows=5000 width=36) (actual time=392.819..422.508 rows=1 loops=1)
Filter: (id = 500000)
Rows Removed by Filter: 999999
CTE cte
-> Seq Scan on foo (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.021..146.183 rows=1000000 loops=1)
Planning Time: 0.201 ms
Execution Time: 427.964 ms
# postgresql 12+
QUERY PLAN
Index Scan using foo_id_ix on foo (cost=0.42..8.44 rows=1 width=37) (actual time=0.045..0.046 rows=1 loops=1)
Index Cond: (id = 500000)
Planning Time: 0.245 ms
Execution Time: 0.077 ms
10, 字符串拼接
‘char’||null在oracle为char，但在postgresql为null
# oracle
select 'char'||null newv from dual;
NEWW
---------
char
# postgresql
select 'char'||null newv;
newv
--------
null
— over —
