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=# create table a(t timestamp not null);
CREATE TABLE
proba2=# insert into a values ('infinity');
INSERT 0 1
proba2=# SELECT * from a;
t
----------
infinity
(1 sor)
proba2=# alter table a alter t type date;
ALTER TABLE
proba2=# SELECT *, t is null as is_null from a;
t | is_null
---+---------
| t
(1 sor)
proba2=# \d a
Tábla "public.a"
Oszlop | Típus | Módosító
--------+-------+----------
t | date | not null
proba2=# 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=# 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