PostgreSQL----Conditional expressions

PostgreSQL supports case,coalesce,nullif,greatest,least conditional expressions, and using them can sometimes simplify many feature implementations.

Test table

Test=#Create TableTbl_test (IDint, namevarchar( +), Sexvarchar(1));CREATE TABLETest=#Insert  intoTbl_testValues(1,'Zhang San','m'),(2,'John Doe','m'),(3,'Harry','F');INSERT 0 3

Case

Case is similar to if/else in other languages, and different operations are performed when different conditions are met.

Example 1. query tbl_test table, if sex equals ' m ' then show ' Male ', if ' f ' shows ' female '

Test=#SelectName Case  whenSex= 'm'  Then 'male' Else 'female' End  asSex fromtbl_test; name|Sex------+-----Tom|male John Doe|male Harry|Female (3Rows

Example 2. Query the number of men and women in the Tbl_test table

Method 1: Query separately

Test=#Select Count(*) asWoman fromTbl_testwhereSex= 'F'; female----  1(1row) test=#Select Count(*) asMan fromTbl_testwhereSex= 'm'; male----  2(1Row

Method 2: Use case one-time query

Test=#Select sum( Case  whenSex= 'm'  Then 1 Else 0 End) asMansum( Case  whenSex='F'  Then 1 Else 0 End) asWoman fromTbl_test, male|female----+----  2 |  1(1Row

COALESCE

COALESCE (value[,...])

No limit to the number of arguments, return the first non-null value in the parameter.

Test=#Select COALESCE(NULL,NULL,1,2,3); COALESCE ----------        1(1row) test=#Select COALESCE(NULL,NULL,' One',' Both'); COALESCE ----------One (1Row

Nullif

Nullif (ARG1,ARG2)

Returns NULL if two parameter values are equal, otherwise returns ARG1.

Test=# \psetNULL 'NULL'NullDisplay is"NULLthe. Test=# Test=# Test=#Select Nullif(1,1); Nullif --------   NULL(1row) test=#Select Nullif(1,2); Nullif --------      1(1Row

Greatest and least

Greatest (value [, ...])
LEAST (value [, ...])

No limit on the number of arguments, and return the maximum and minimum values in the entry parameters, respectively

Test=#SelectGreatest (1,2,3,4); Greatest----------        4(1row) test=#SelectGreatest ('a','b','C','D'); Greatest----------D (1row) test=#SelectLeast'a','b','C','D'); Least-------A (1row) test=#SelectLeast1,2,3,4); Least-------     1(1Row

PostgreSQL----Conditional expressions