How to cast a varchar into an int[] using 'in' operator in PostgreSQL

Question

I am having a hard time about this. I am trying to cast a varchar containing a list of numbers into an int array, in order to serve an in operator on a where clause. This is the last version of my code.

create or replace function is_product_in_categories (
    _product_id integer,
    _cat_list   varchar
)
returns boolean
as $$
declare
    _n integer;
begin
    _n = 0;

    select count(*) 
    into _n
    from category_products 
    where product_id = _product_id and category_id in (_cat_list::int[]);

  return _n > 0;
end;
$$ language plpgsql;


select is_product_in_categories(1, '1, 2, 3');

Error is

 SQL Error [42883]: ERROR: operator does not exist: integer = integer[]
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function is_product_in_categories(integer,character varying) line 7 at SQL statement

I have tried several arguments, as '1, 2, 3', '(1, 2, 3)' or '[1, 2, 3]'. Also removing parenthesis near the in operator, etc.

Any idea?

The postgres array syntax is `'{1, 2, 3}'`. But you probably don't want that - instead, you should either split the string by a separator, or make your function take an array parameter in the first place. — Bergi, Jul 14 '20 at 20:16
Thansk Bergi, but I still have a syntax error using `'{1, 2, 3}'` . The error is `SQL Error [42883]: ERROR: operator does not exist: integer = integer[]` meaning that the validator is interpreting the contents of the parenthesis as a single number. — coterobarros, Jul 14 '20 at 22:03
The problem seems to be that the `in` operator does not accept an array. For example, `category_id in (array [1,2,3])` raises the same exception. — coterobarros, Jul 14 '20 at 22:07

score 2 · Answer 1 · answered Jul 14 '20 at 20:12

Use string_to_array() to convert a string to a (text) array:

SELECT string_to_array('1, 2, 3', ', ')::int[]; -- use ::int[] to cast to an int array
+---------------+
|string_to_array|
+---------------+
|{1,2,3}        |
+---------------+

If you control the string (e.g you are constructing it yourself), you can use either of those two:

SELECT ARRAY[1, 2, 3]      -- no need to cast this one
     , '{1, 2, 3}'::int[]; -- you have to specify that it's an array, not simply a string value
+-------+-------+
|array  |int4   |
+-------+-------+
|{1,2,3}|{1,2,3}|
+-------+-------+

score 1 · Accepted Answer · answered Jul 14 '20 at 22:24

The problem with the in operator is it doesn't admit an array as an argument. Instead it expects a simple list of scalars. See PostgreSQL documentation here https://www.postgresql.org/docs/9.0/functions-comparisons.html#AEN16964

To avoid this limitation the = any combination accepts an array as an argument. The code ends this way.

create or replace function is_product_in_categories (
  _product_id integer,
  _cat_list   varchar
)
returns boolean
as $$
declare
  _n integer;
begin
  _n = 0;

  select count(*) 
  into _n
  from of_category_products 
  where product_id = _product_id and category_id = any (_cat_list::int[]);

  return _n > 0;
end;
$$ language plpgsql;

select is_product_in_categories(1, '{1, 2, 3}')

Also, the syntax for literal arrays, using {} has been observed, following Bergi comment.

score 1 · Answer 3 · answered Jul 14 '20 at 23:28

Revise your function declaration and define as variadic integer array:

create or replace function is_product_in_categories (
    _product_id integer,
    Variadic _cat_list  integer[] )

or just as a array of integers:

create or replace function is_product_in_categories (
_product_id integer,
_cat_list  integer[] )

Either way you can reduce the function to a single statement.

create or replace function is_product_in_categories3 (
    _product_id integer,
      _cat_list integer[]
)
returns  boolean
language sql
as $$
    select 
      exists (select null 
               from category_products
              where product_id = _product_id and category_id = any(_cat_list)
             ); 
$$;

See here for full example of both.

Thank you Belayer. This solutions is even cleaner than mine. I appreciate the `select exists` approach to the purpose of the function. — coterobarros, Jul 15 '20 at 11:34

How to cast a varchar into an int[] using 'in' operator in PostgreSQL

3 Answers3