2

Here is my schema and data

create table mytable (id numeric, val text);
create table mytable1 (id1 numeric, id text);

insert into mytable values (123, 'aaa');
insert into mytable values (124, 'bbb');
insert into mytable values (125, 'ccc');
insert into mytable1 values (1001, '[123]');
insert into mytable1 values (1002, '[123,124]');
insert into mytable1 values (1003, '[123,124,125]');

When I am running the below query, I am getting expected result.

select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] from mytable1 mt1 where mt1.id1 = 1003

Result:

123,124,125

But, when I am passing the above query as inner query for a select query, I am not getting the result

select mt.val from mytable mt where mt.id = any (select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] from mytable1 mt1 where mt1.id1 = 1003)

Expected result:

val
---
aaa
bbb
ccc

Anything wrong in the query?

(using Postgresql-9.1)

1

Your first query isn't actually giving you what you would expect (at least on my PostgreSQL 9.1 installation)

pgsql=# select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] from mytable1 mt1 where mt1.id1 = 1003;
 string_to_array 
-----------------
 {123,124,125}
(1 row)

pgsql=#

This is returning an array result, not 123,124,125 as you indicated.

Trying your second query as is results in the following output:

pgsql=# select mt.val from mytable mt where mt.id = any (select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] from mytable1 mt1 where mt1.id1 = 1003);
ERROR:  operator does not exist: numeric = numeric[]
LINE 1: select mt.val from mytable mt where mt.id = any (select stri...
                                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
pgsql=#

Which makes sense, since you're trying to compare a numeric type column with a numeric[] array type column.

Unnesting your numeric[] array into a straight numeric type like this:

pgsql=# select unnest(string_to_array(trim(mt1.id, '[]'), ',')::numeric[]) from mytable1 mt1 where mt1.id1 = 1003;
 unnest 
--------
    123
    124
    125
(3 rows)

pgsql=# 

Should let you do the ANY comparison you're wanting to do, and get the result you're looking for.

pgsql=# select mt.val from mytable mt where mt.id = any (select unnest(string_to_array(trim(mt1.id, '[]'), ',')::numeric[]) from mytable1 mt1 where mt1.id1 = 1003);
 val 
-----
 aaa
 bbb
 ccc
(3 rows)

pgsql=#
| improve this answer | |
3

The problem is that:

select string_to_array(trim(mt1.id, '[]'), ',')::numeric[]

does not return an array, it returns a set of arrays (even though it's just a single row - it's still a set)

If you are sure that your sub-query always returns a single row, you can simply cast the result of the select to an array:

select mt.val 
from mytable mt 
where mt.id = any ((select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] 
                   from mytable1 mt1 
                   where mt1.id1 = 1003)::numeric[])

You actually don't need the cast twice, so the above can be simplified by removing the inner cast:

select mt.val 
from mytable mt 
where mt.id = any ((select string_to_array(trim(mt1.id, '[]'), ',')
                   from mytable1 mt1 
                   where mt1.id1 = 1003)::numeric[])
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.