Thread: union in an in clause and timestamp

Okay, I'm running into two things that I would expect to work. 
I've included a simple test case for both to reproduce the problem.  

1) Obviously, the first two work and the third does not. 
are these bugs?

2) Cannot create index on timestamp colmun

basement=> select version();
version                                                            
-------------------------------------------------------------------
PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)
basement=> select 'hello' where 1 in (select 1);
?column?
--------
hello   
(1 row)
basement=> select 'hello' where 1 in (select 2);
?column?
--------
(0 rows)
basement=> select 'hello' where 1 in (select 2 union select 1);
ERROR:  parser: parse error at or near "union"
basement=> 


And then, I find that I cannot create an index on a 
timestamp column;

basement=> create table ts (t timestamp);
CREATE
basement=> create index ttt on ts(t); 
ERROR:  Can't find a default operator class for type 1296.
basement=> 


-- 
The world's most ambitious and comprehensive PC game database project.
                     http://www.mobygames.com

Brian Hirt <bhirt@mobygames.com> writes:
> Okay, I'm running into two things that I would expect to work. 

> basement=> select 'hello' where 1 in (select 2 union select 1);
> ERROR:  parser: parse error at or near "union"

UNION isn't currently supported in sub-selects.  Hopefully we can make
it work after the long-threatened querytree redesign.  But right now,
the union code is so crufty that no one wants to touch it...

> And then, I find that I cannot create an index on a 
> timestamp column;
> basement=> create index ttt on ts(t); 
> ERROR:  Can't find a default operator class for type 1296.

For the moment, use one of the other time-related types instead.
After the dust settles from Thomas' upcoming consolidation of the
date/time datatypes, I expect everything that remains will have a
complete set of operators and index support.
        regards, tom lane