Обсуждение: alter column type (from timestamp to date) violates not null

Hi!

When change a column type from timestamp to date, the 'infinity' and '-
infinity' values will be NULL. Even if the column has a not null
constraint.
Sorry, if it is a duplicated bug report.

Regards,
psmith

Here is the test procedure:

proba2=3D# create table a(t timestamp not null);
CREATE TABLE
proba2=3D# insert into a values ('infinity');
INSERT 0 1
proba2=3D# SELECT * from a;
    t
----------
 infinity
(1 sor)

proba2=3D# alter table a alter t type date;
ALTER TABLE
proba2=3D# SELECT *, t is null as is_null from a;
 t | is_null
---+---------
   | t
(1 sor)

proba2=3D# \d a
     T=E1bla "public.a"
 Oszlop | T=EDpus | M=F3dos=EDt=F3
--------+-------+----------
 t      | date  | not null

proba2=3D# select version();
                                                   version
---------------------------------------------------------------------------=
-----------------------------------
 PostgreSQL 8.1.0 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3
20051023 (prerelease) (Debian 4.0.2-3)
(1 sor)

proba2=3D# select 'infinity'::timestamp::date is null;
 ?column?
----------
 t
(1 sor)

On Fri, Mar 09, 2007 at 04:55:59AM -0800, psmith wrote:
> When change a column type from timestamp to date, the 'infinity' and '-
> infinity' values will be NULL. Even if the column has a not null
> constraint.
[...]
>                                                    version
> --------------------------------------------------------------------------------------------------------------
>  PostgreSQL 8.1.0 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3
> 20051023 (prerelease) (Debian 4.0.2-3)
> (1 sor)

This appears to be fixed already:

test=> alter table a alter t type date;
ERROR:  column "t" contains null values
test=> select version();
                                  version
---------------------------------------------------------------------------
 PostgreSQL 8.1.8 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
(1 row)

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Fri, Mar 09, 2007 at 04:55:59AM -0800, psmith wrote:
>> When change a column type from timestamp to date, the 'infinity' and '-
>> infinity' values will be NULL. Even if the column has a not null
>> constraint.

> This appears to be fixed already:

http://archives.postgresql.org/pgsql-bugs/2006-07/msg00015.php
http://archives.postgresql.org/pgsql-committers/2006-07/msg00067.php

            regards, tom lane