Skip to content

Instantly share code, notes, and snippets.

@nathansgreen
Created January 5, 2018 16:57
Show Gist options
  • Save nathansgreen/f9b527de5d5691de0249c9c4dc61ec10 to your computer and use it in GitHub Desktop.
Save nathansgreen/f9b527de5d5691de0249c9c4dc61ec10 to your computer and use it in GitHub Desktop.
PostgreSQL <9.5 array_position function
-- The array_position function was added in Postgres 9.5.
-- For older versions, you can get the same behavior with this function.
create function array_position(arr ANYARRAY, elem ANYELEMENT, pos INTEGER default 1) returns INTEGER
language sql
as $BODY$
select row_number::INTEGER
from (
select unnest, row_number() over ()
from ( select unnest(arr) ) t0
) t1
where row_number >= greatest(1, pos)
and (case when elem is null then unnest is null else unnest = elem end)
limit 1;
$BODY$;
@andreslemik
Copy link

Great! Thank you!!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment