Postgres: Check function input array for null or empty or containing null

Question

I am looking to validate argument input to check, for example, that the _product_ids::BIGINT[] is not null, or does not contain null (or optionally, does not contain only null), or is not empty.

Here is what I have:

IF (
  _product_ids IS NULL -- Is null
  OR -1 = ANY(_product_ids) IS NULL -- Contains null
  OR COALESCE(ARRAY_LENGTH(_product_ids, 1) < 1, TRUE) -- Is empty
) THEN
  RAISE EXCEPTION 'INPUT IS INVALID';
END IF;

I would like to tweak 'Contains null' so that it'll return true only if there is only nulls. Additionally, I would like to know if there is a way to check for null and empty array at the same time.

PostgresSQL 9.6 is used.

klin · Accepted Answer · 2018-04-25 20:27:46Z

1

The expression may look like this:

_product_ids is null -- is null
or cardinality(_product_ids) = 0 -- is empty
or (select bool_and(e is null) from unnest(_product_ids) e) -- contains only nulls

answered Apr 25, 2018 at 20:27

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Over a year ago

Should I be concerned about performance of unnest if the size of the array is expected to be below <= 16?

klin Over a year ago

Surely the expression is more expensive than a simple compare with any() but I don't think the difference if very significant.

Collectives™ on Stack Overflow

Postgres: Check function input array for null or empty or containing null

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related