All Products
Search

This Product

    Hologres:Conditional functions

    Document Center

    Hologres:Conditional functions

    Last Updated:Oct 20, 2023

    Hologres is compatible with PostgreSQL and allows you to use standard PostgreSQL syntax for data development. Hologres supports a subset of PostgreSQL functions. This topic describes the conditional functions that are supported by Hologres and sample statements of these functions.

    For more information about how to use conditional functions, see Conditional functions.

    Note

    In the following table, the sample statements are executed on the test table and the execution results are provided in the Result column. The test table contains the following data: 

     a
---
 1
 2
 3

    Function

    Description

    Sample statement

    Result

    case

    Goes through conditions until a condition is determined to be true and then returns a value. 

    SELECT a,
  CASE WHEN a=1 THEN 'one'            
       WHEN a=2 THEN 'two'            
       ELSE 'other'       
  END    
FROM test;
    a    case
----+-----------
1    one
2    two
3    other

    coalesce

    Returns the value of the first expression in the list that is not null.

    Note

    This function returns null only if all expressions are null. 

    • select COALESCE(1,2);
    • select COALESCE(NULL,2,1);
    • select COALESCE(NULL,0);

    • 1

    • 2

    • 0

    nullif

    Compares two expressions and returns a value. If the expressions are equal, the function returns null. If they are not equal, the value of the first expression is returned. 

    SELECT a,       
      nullif('a','a')    
FROM test;
    a    nullif
----+-----------
1    
2    
3

    greatest

    Returns the largest value from a list of expressions. 

    SELECT a,       
      greatest('a','b','c')    
FROM test;
    a    greatest
----+-----------
1    c
2    c
3    c

    least

    Returns the smallest value from a list of expressions. 

    SELECT a,       
      least('a','b','c')    
FROM test;
    a    least
----+-----------
1    a
2    a
3    a