Re: [BUGS] alter column type (from timestamp to date) violates not null

[Michael Fuhr]

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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [BUGS] alter column type (from timestamp to date) violates not null

[Tom Lane]

Michael Fuhr [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[BUGS] alter column type (from timestamp to date) violates not null

[psmith]

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)


---(end of broadcast)---
TIP 6: explain analyze is your friend