PostgreSQL DBA(136) - Develop(Common Mistakes)

本节简单介绍了在PostgreSQL中容易出错但又容易被忽略的细节。

除法运算
在整数和整数之间执行除法运算时，结果会是整数，但实际上我们希望保留小数位。

[local:/data/pg12]:5432 pg12@testdb=# select 1/2; ?column? ----------        0(1 row)[local:/data/pg12]:5432 pg12@testdb=#

查询操作符"/"的定义

[local:/data/pg12]:5432 pg12@testdb=# select oprname,oprleft::regtype,oprright::regtype,oprresult::regtype from pg_operator where oprname = '/'; oprname |     oprleft      |     oprright     |    oprresult     ---------+------------------+------------------+------------------ /       | smallint         | smallint         | smallint /       | integer          | integer          | integer /       | smallint         | integer          | integer /       | integer          | smallint         | integer /       | real             | real             | real /       | double precision | double precision | double precision /       | bigint           | bigint           | bigint /       | bigint           | integer          | bigint /       | integer          | bigint           | bigint /       | bigint           | smallint         | bigint /       | smallint         | bigint           | bigint /       | point            | point            | point /       | path             | point            | path /       | box              | point            | box /       | money            | real             | money /       | money            | double precision | money /       | money            | bigint           | money /       | money            | integer          | money /       | money            | smallint         | money /       | money            | money            | double precision /       | real             | double precision | double precision /       | double precision | real             | double precision /       | circle           | point            | circle /       | interval         | double precision | interval /       | numeric          | numeric          | numeric(25 rows)[local:/data/pg12]:5432 pg12@testdb=#

在PG中，整型之间的除法得到的结果为整型，如结果需要得到浮点数或实数，则需要执行类型转换，比如把其中一个算子转换为float或者numeric

[local:/data/pg12]:5432 pg12@testdb=# select 1/2::float; ?column? ----------      0.5(1 row)[local:/data/pg12]:5432 pg12@testdb=# select 1/2::numeric;        ?column?        ------------------------ 0.50000000000000000000(1 row)[local:/data/pg12]:5432 pg12@testdb=#

被0除错误
如除数为0时，会报"ERROR: division by zero"错误，为免出现这种错误，可使用nullif判断除数是否为0，如为0则返回null

[local:/data/pg12]:5432 pg12@testdb=# select 1/0;ERROR:  division by zero[local:/data/pg12]:5432 pg12@testdb=# select 1/nullif(0,0); ?column? ----------(1 row)[local:/data/pg12]:5432 pg12@testdb=# select 1/nullif(0,0) is null; ?column? ---------- t(1 row)[local:/data/pg12]:5432 pg12@testdb=#

统计NULL值问题
如使用count(column)时，column的值如为null则不会统计到结果中，使用count(*)则会统计。

[local:/data/pg12]:5432 pg12@testdb=# create table t_count(id int);CREATE TABLE[local:/data/pg12]:5432 pg12@testdb=# insert into t_count select generate_series(1,1000);INSERT 0 1000[local:/data/pg12]:5432 pg12@testdb=# insert into t_count select null from generate_series(1,1000);INSERT 0 1000[local:/data/pg12]:5432 pg12@testdb=# select count(*) from t_count; count -------  2000(1 row)[local:/data/pg12]:5432 pg12@testdb=# select count(id) from t_count; count -------  1000(1 row)[local:/data/pg12]:5432 pg12@testdb=#

参考资料
https://hakibenita.com/sql-dos-and-donts