Postgres SQL 7.1 a thank you and a possible bug
First of all 7.1 is great! I'm using beta4 and it seems to be working
very well.
Thank's particularly for the SQL92 join syntax (though union joins
would be usefull).
I have a problem though and I'm not sure if it's a bug or not so I
thought I'd discuss it here first.
The synopsis of the problem is that date columns don't seem to be
working as dates when they're selected through a union view.
For example
create table A
( id INTEGER,
date DATE);
insert into table A values (1,'1 dec 1999');
create table B
( id INTEGER,
somedate DATE);
insert into table B values (2,'3 jan 2000');
create view C as
select id,date from A
union
select id,somedate from B;
select * from C where date<'1 jan 2000';
id date
----------------------------
1 '1999-12-01'
2 '2000-01-03'
select * from C where date<'1 jan 2001';
id date
----------------------------
1 '1999-12-01'
2 '2000-01-03'
Which seems strange especially as this:
\d C
produces the following:
Attribute | Type | Modifier
-----------+------+----------
id | integer |
date | date |
I've tried casting the date in various ways but none of it works.
Is this a bug or am I doing something gloriously stupid?
Nic Ferrier
"Nic Ferrier" <nferrier@tapsellferrier.co.uk> writes:
The synopsis of the problem is that date columns don't seem to be
working as dates when they're selected through a union view.
No, the problem is that restriction clauses applied to views that
contain unions are busted in beta4 :-(. Thanks for the bug report!
It's fixed in current CVS, or use tomorrow morning's snapshot.
regards, tom lane