Search code examples
sqlarrayspostgresqlcastingsql-in

How to cast arrays to a type?

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.

Solution

  • 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: