Processing of null values in PostgreSQL

Processing of null values in PostgreSQL

When a field value is null in the addition, subtraction, multiplication, division, and Division operations, the output results often disappoint us and we cannot get the expected values, you can use coalesce to convert a null field to a default value to improve the operation effect.

Coalesce function usage: coalesce (field name, default value), simple ~

Demo: [root @ dbserver ~] # Su-postgres-bash-3.2 $ psql music psql (9.5beta2) Input "help" to obtain help information. music = # create table test_null (id int, num1 int, num2 int); CREATE TABLE

Insert data. Some values are normal numbers, and others are null values: null: music = # INSERT into test_null values (1,100,100); insert 0 1 music = # insert into test_null values (2,200,200 ); INSERT 0 1 music = # insert into test_null values (3, null, 100); INSERT 0 1 music = # insert into test_null values (4,300, null ); INSERT 0 1 music = # insert into test_null values (5, null, 500); INSERT 0 1

Check the data in the table and find that all null fields are null: music = # select * from test_null; id | num1 | num2 ---- + ------ 1 | 100 | 100 2 | 200 | 200 3 | 100 4 | 300 | 5 | 500 (5 rows of Records)



When you view the result of a direct operation, you will find that all the operation results for null values are null: music = # select num1 + num2 from test_null ;? Column? ---------- 200 400 (5 rows of Records)



After using the coalesce function, you can solve the null problem: music = # select coalesce (num1, 0) + coalesce (num2, 0) from test_null ;? Column? ---------- 200 400 100 300 500 (5 rows of Records)

Here, the default value of coalesce is 0. You can also set it to another value. For example, you can set the default value of human body temperature to 36, freezing point to 0, and boiling point to 100.