Hologres:Conditional functions

Sample data

This topic uses data in the test table to describe how to use the CASE, NULLIF, GREATEST, and LEAST functions. The following sample code is used to create the test table and insert data into the table.

CREATE TABLE test (
    a INT
);
INSERT INTO test (a) VALUES (1);
INSERT INTO test (a) VALUES (2);
INSERT INTO test (a) VALUES (3);

CASE

• Description: Goes through conditions until a condition evaluates to true and then returns a value.

• Example:

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

  The following result is returned:

  a    case
  ----+-----------
  1    one
  2    two
  3    other

IF

Only Hologres V2.1 and later support the IF function. If the version of your Hologres instance is V2.0 or earlier, upgrade your Hologres instance. For more information, see Instance upgrades.

• Description: Selects a statement to execute based on a specific condition.

  IF(condition, value_if_true, value_if_false)

  condition: the condition. If the result of the condition is TRUE, the second parameter that is specified in the function is returned. If the result of the condition is FALSE or NULL, the third parameter that is specified in the function is returned.

• Example:

  DROP TABLE IF EXISTS if_test;
  CREATE TABLE if_test (
      id int,
      name text,
      age int
  );
  
  INSERT INTO if_test VALUES ('1', 'a', '18'), ('2', 'b', '19'), ('3', 'c', '25'), ('4', 'd', '8'), ('5', 'e', '27'), ('6', 'f', '21');
  
  SELECT
      name,
      IF (age >= 18, 'Adult', 'Minor')
  FROM
      if_test;

  The following result is returned:

  +-------+---------+
  | name  | if      |
  +-------+---------+
  | a     |  Adult   |
  | c     |  Adult   |
  | e     |  Adult   |
  | f     |  Adult   |
  | d     |  Minor |
  | b     |  Adult   |

COALESCE

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

  Note

  This function returns null if all expressions are null.

• Example:

  -- The value 1 is returned.
  SELECT COALESCE(1,2);
  
  -- The value 2 is returned.
  select COALESCE(NULL,2,1);
  
  -- The value 0 is returned.
  select COALESCE(NULL,0);

NULLIF

• Description: Evaluates whether the values of two expressions are the same and returns a value. If the values are the same, this function returns null. If the values are different, this function returns the value of the first expression.

• Example:

  SELECT a,       
        NULLIF('a','a')    
  FROM test;

  The following result is returned:

  a    nullif
  ----+-----------
  1    
  2    
  3    

GREATEST

• Description: Returns the largest value from a list of expressions.

• Example:

  SELECT a,       
        GREATEST('a','b','c')    
  FROM test;

  The following result is returned:

  a    greatest
  ----+-----------
  1    c
  2    c
  3    c

LEAST

• Description: Returns the smallest value from a list of expressions.

• Example:

  SELECT a,       
        LEAST('a','b','c')    
  FROM test;

  The following result is returned:

  a    least
  ----+-----------
  1    a
  2    a
  3    a

References

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