SQL - IN Operator get Position

Question

Is it possible to get the position (index) of first argument of a list of arguments with a operator as IN?

The result I'm looking for is something as next:

SELECT 2 IN(2, 3, 1); -- Result I want is 0 but with IN is true
SELECT 3 IN(2, 3, 1); -- Result I want is 1 but with IN is true
SELECT 0 IN(2, 3, 1); -- Result I want is -1 but with IN is false
SELECT 1 IN(1, 1, 3); -- RESULT I WANT IS 0 ,1 but with IN is true

Answer 1

You can't do this with a "plain" IN clause, but you can do this with an array and the unnest function:

select t.idx
from unnest(array[2,3,1]) with ordinality t(v,idx)
where t.v = 2;

select t.idx
from unnest(array[1, 1, 3]) with ordinality t(v,idx)
where t.v = 1;

However, if the value to search for is not in the array, you will get no rows at all.

Answer 2

in postgres can useful function POSITION, return first index in string example:

select position('2' in '2,3,1,2') // return 1