Postgres SQL 7.1 a thank you and a possible bug

From: "Nic Ferrier" <nferrier(at)tapsellferrier(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Postgres SQL 7.1 a thank you and a possible bug
Date: 2001-02-02 19:32:38
Message-ID: sa7b0cef.060@tapsellferrier.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Daniel Clark 2001-02-02 21:12:22 Programmer's Guide
Previous Message Stephan Szabo 2001-02-02 19:14:48 Re: plpgsql always returning null..