PostgreSQL DBA (136)-Develop (Common Mistakes)

This section provides a brief introduction to the details that are error-prone but easily overlooked in PostgreSQL.

Division operation

When you divide between integers and integers, the result is an integer, but in fact we want to keep the decimal places.

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

Definition of query operator "/"

[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=#

In PG, the result of division between integers is an integer. If the result requires floating-point or real numbers, you need to perform type conversion, such as converting one of the operators to float or numeric.

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

Error divided by 0

A "ERROR: division by zero" error will be reported if the divisor is 0. To avoid this error, you can use nullif to determine whether the divisor is 0, and return null if it is 0.

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

Statistical null value problem

For example, when using count (column), the value of column will not be counted in the result if null is used, but it will be counted if count (*) is used.

[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 (1jue 1000); INSERT 0 1000 [local:/data/pg12]: 5432 pg12@testdb=# insert into t_count select null from generate_series (1je 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 tweeter; count-1000 (1 row) [local:/data/pg12]: 5432 pg12@testdb=#

references

Https://hakibenita.com/sql-dos-and-donts