Select query not giving result for inner query values

Question

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)

Kassandry · Accepted Answer · 2015-10-20 07:12:21Z

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

user1822 · Accepted Answer · 2015-10-20 07:22:54Z

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[])

Stack Exchange Network

Select query not giving result for inner query values

2 Answers 2

Your Answer

Hot Network Questions

Select query not giving result for inner query values

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions