PostgreSQL Select Query – Troubleshooting Select Query Not Giving Result for Inner Query Values

postgresql

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)

Best Answer

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=#
Related Question