首页 » ORACLE 9i-23ai, PostgreSQL/GaussDB » 10个PostgreSQL中常见SQL错误

10个PostgreSQL中常见SQL错误

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 —

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：