Count zeros in starting of an integer array until a non zero element in PostgresSQL-CodePudding

I have an integer array column in the Postgres table. Need to count the number of zeros from starting of the array until a non-zero element is encountered. If all zeros are present then return -1

For example:

int_arr_col           count_zeros_start
------------          ------------------
{0,0,24,34,0,0,34}           2
{0,0,0,0,0}                  -1
{34,34,89,0,0,0}             0
{0,0,0,0,45}                 4
{0,20,80}                    1
{38,80,42}                   0

Only while loops seem the option is there any simpler method.

CodePudding user response：

I guess you could use UNNEST … WITH ORDINALITY for this, getting the position of the first non-zero element:

COALESCE((
  SELECT ord-1
  FROM UNNEST(int_arr_col) WITH ORDINALITY AS els(el, ord)
  WHERE el != 0
  ORDER BY ord
  LIMIT 1
), -1)

^{(online demo, also including some edge cases around NULLs, non-standard lower bounds and multidimensional arrays)}