Use integer array in IN condition PostgreSQL

Question

I have integer Array like {10,13,20}

I have to pass this array in IN condition like

SELECT count(*) FROM table WHERE id IN (10,20,30)

I tried:

SELECT count(*) FROM table WHERE id IN(array_to_string(_array,','))

but it's throwing the error casting operator does not exist: bigint = text

Thanks in advance

What is _array? What are you trying to achieve by using array_to_string? — Bergi
– Bergi, Commented Dec 29, 2021 at 14:05

Pooya · Accepted Answer · 2021-12-29 13:57:40Z

1

you can use below query:

Demo

SELECT count(*) FROM test WHERE id = any(array[10,20,30]::int[]);

answered Dec 29, 2021 at 13:57

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

6 Comments

Krunal Pandya Over a year ago

I passed array in {10,20,30} this format.

Krunal Pandya Over a year ago

I have array which in this format : {10,20,30} Not in this format : array[10,20,30]

Pooya Over a year ago

Not diffrence between array[10,20,30]::int[] and {10,20,30}. You can use both of them dbfiddle.uk/…

Krunal Pandya Over a year ago

You are right but when pass array in stored procedure its show "{10,20,30}" and throw the error

Bergi Over a year ago

@KrunalPandya Please edit your question to show the entire stored procedure definition. Is _array really of type int[] (or bigint[])?

|

Stefanov.sm · Accepted Answer · 2021-12-30 08:18:54Z

0

Use string_to_array instead of array_to_string. Cast the resulting array to bigint[] as the type of id is bigint. Here it is, easy to be parameterized:

SELECT count(*) FROM users u
 WHERE id = any(string_to_array('10,20,30',',')::bigint[]);

After your clarification that _array is an argument or variable of type bigint[] then simply:

SELECT count(*) FROM users u WHERE id = any(_array);

edited Dec 30, 2021 at 8:18

answered Dec 29, 2021 at 14:03

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

4 Comments

Bergi Over a year ago

"the type of id is bigint." - uh, how do you know? The OP didn't share their table definition.

Stefanov.sm Over a year ago

@Bergi The OP shared the error message 'operator does not exist: bigint = text'

Krunal Pandya Over a year ago

it's like this SELECT count(*) FROM users u WHERE id = any(string_to_array({10,20,30},',')::bigint[]); Its work ?

Stefanov.sm Over a year ago

No, string_to_array expects its first argument to be text, no curly braces are needed. string_to_array('10,20,30',','). Pls. see updated answer.

Collectives™ on Stack Overflow

Use integer array in IN condition PostgreSQL

2 Answers 2

6 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Related