How to cast arrays to a type?

Question

I want this query to return all ids and associated emails that are NOT returned by query #2:

select my_table.id, my_table.email
from my_table join another_table on my_table.id=another_table.mytable_id
where my_table.id not in (select array (select my_table.id 
             from my_table join yetanother_table 
             on my_table.id = yetanother_table.mytable_id 
             where yetanother_table.time1 between '2015-01-26T08:00:00.000Z'
                                              and '2015-02-02T07:59:59.999Z'
             group by my_table.id))

When I run it, I get the following error near the select in line 3:

operator does not exist: integer = integer[]   
You might need to add explicit type casts.

I tried casting the array to integer and got this:

cannot cast type integer[] to integer

I also tried casting both my_table.id and the array to varchar. That got rid of the error but returned fewer rows than I expected!

First questions: Why can't I cast integer[] to integer? What is the default data type of an array? Is there a cleaner way to get rid of the "operator does not exist" error than casting both my_table.id and the array to varchar?

And following up: now I'm wondering why I'm getting too few rows. Does it look like the way I wrote it will return what I want? i.e. all ids that are not returned by query #2?

Another constraint - I need to do everything with one statement.

Just get rid of the `select array(` part. You don't need that when using an `IN` operator — , Feb 03 '15 at 18:29

score 1 · Accepted Answer · edited May 23 '17 at 12:20

Once you remove the misplaced array constructor, you get a working query.
The IN and NOT IN constructs require a plain subquery to the right yielding matching types - not an array.

The query is still twisted and inefficient, though. Use instead:

select m.id, m.email
from   my_table m
join   another_table a on a.mytable_id = m.id
left   join yetanother_table y on y.mytable_id = m.id
                              and y.time1 >= '2015-01-26 08:00'
                              and y.time1 <  '2015-02-02 08:00'
where  y.mytable_id IS NULL;

More information:

Select rows which are not present in other table

score 0 · Answer 2 · answered Feb 03 '15 at 18:29

0

You need to get rid of the select array. The IN operator is looking for a single column query result, not an array.

answered Feb 03 '15 at 18:29

Politank-Z

3,653
3
24
28

How to cast arrays to a type?

2 Answers2