How to return empty array in a case block?-CodePudding

working on a trigger in Postgres. Currently running into an issue where I'm trying to set a value as either an empty array or an array with just the one NEW value, depending on if there is a NEW value or not:

project_ids =
  CASE NEW.project_tracking_id
    WHEN null THEN ARRAY
    ELSE ARRAY[NEW.project_tracking_id]
  END

"Set project_ids to either [] (if null) or [NEW.project_tracking_id]"

The else block works as expected and will store that new value as the first element in a new array. The WHEN null THEN ARRAY part does not work for me though. It just adds null to the array, producing [null].

How do I specify that it should just be an empty array in that case? Not sure if this helps, but my sequelize type for that field is:

type: Sequelize.ARRAY(Sequelize.STRING),
defaultValue: [],

CodePudding user response：

project_ids =
  CASE NEW.project_tracking_id
    WHEN null THEN ARRAY[]::XXX[]   //XXX is the type of the array.
    ELSE ARRAY[NEW.project_tracking_id]
  END

CodePudding user response：

The problem is that your CASE expression implicitly compares NEW.project_tracking_id = NULL, which is never true, even if the left side is NULL. Use the other form of CASE:

CASE WHEN NEW.project_tracking_id IS NULL
     THEN ARRAY[]::integer[]
     ELSE ARRAY[NEW.project_tracking_id]
END

This assumes that the data type of NEW.project_tracking_id is integer; change the array type if that is not the case.

A shorter version of the above would be

coalesce(NEW.project_tracking_id, ARRAY[]::integer[])