How to deal with PostgreSQL null value null participation in operation_PHP tutorial

How to deal with PostgreSQL null value participating in the operation

When a field value is null (null) is encountered in digital addition, subtraction, multiplication and division operations, output The results often disappoint us, and we do not get the values ​​we expect. We can use coalesce to convert all fields whose values ​​are null, that is, empty values, into default values ​​for calculation, thereby improving the calculation effect.

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

The demonstration is as follows: [root@dbserver ~]# su - postgres -bash-3.2$ psql music psql (9.5beta2) Enter "help" to get help information. music=# create table test_null(id int,num1 int,num2 int); CREATE TABLE

Insert data, some values ​​are normal numbers, some INSERT 0 1 music=# insert into test_null values(3,null,100); 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, It is found that all fields with null values ​​are null: music=# select * from test_null; id | num1 | num2 ---- ------ ------ 1 | 100 | 100 2 | 200 | 200 3 | 100 4 | 300 | 5 | select num1 num2 from test_null; ?column? ---------- 200 400 (5 lines of records)



Use the coalesce function to handle the null problem: music =# select coalesce(num1,0) coalesce(num2,0) from test_null; ?column? ---------- 200 400 100 300 500 (5 lines of records)

Here, The default value set by coalesce is 0, and it can also be set to other values. For example, the default value of human body temperature can be set to 36, the freezing point is 0, the boiling point is 100, etc.



http://www.bkjia.com/PHPjc/1112547.html

www.bkjia.com