ALTER TYPE ... USING(NULL) / NOT NULL violation

Started by Alexander M. Pravkingalmost 20 years ago3 messagesbugs
Jump to latest
#1Alexander M. Pravking
fduch@antar.bryansk.ru

If a column type is being changed with USING (NULL) clause,
NOT NULL constraint seems not to be checked:

fduch@~=# CREATE table test (x integer not null);
CREATE TABLE
fduch@~=# INSERT INTO test VALUES (1);
INSERT 0 1
fduch@~=# ALTER TABLE test ALTER x TYPE boolean USING (NULL);
ALTER TABLE
fduch@~=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
x | boolean | not null

fduch@~=# SELECT * from test;
x
---

(1 row)

fduch@~=# SELECT version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518
(1 row)

8.0 branch has the same behavour.

--
Fduch M. Pravking

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alexander M. Pravking (#1)
Re: ALTER TYPE ... USING(NULL) / NOT NULL violation

"Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:

If a column type is being changed with USING (NULL) clause,
NOT NULL constraint seems not to be checked:

Hm, the code seems to have provisions to check "SET NOT NULL", but
it fails to check pre-existing NOT NULL attributes :-(

regards, tom lane

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alexander M. Pravking (#1)
Re: ALTER TYPE ... USING(NULL) / NOT NULL violation

"Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:

If a column type is being changed with USING (NULL) clause,
NOT NULL constraint seems not to be checked:

I've committed patches for this. Thanks for the report.

regards, tom lane