The following bug has been logged online:
Bug reference: 4751
Logged by: Grzegorz Junka
Email address: xgjx@poczta.onet.pl
PostgreSQL version: 8.3.7
Operating system: FreeBSD 7.1
Description: Incorrect pg_dump output when dropping not null in
inherited table.
Details:
I don't know if these steps below are allowed, but documentation doesn't say
anything that they are not. If they are not allowed then an error should be
raised. If they are allowed then pg_dump should create the schema correctly.
In either way there is a bug somewhere.
Basically what I am trying to do is inheriting a table containing a NOT NULL
constraint, dropping the constraint in the inherited table, and then dumping
the database schema to a file. The output file doesn't have the NOT NULL
constraint dropped in the inherited table.
Steps:
create table parent_table (someint integer not null);
create table child_table (id integer) inherits (parent_table);
alter table only child_table alter column someint drop not null;
Verify that it works:
insert into parent_table (someint) values (null);
ERROR: null value in column "someint" violates not-null constraint
insert into child_table (someint) values (null);
INSERT 0 1
Now dump the database:
pg_dump database >mydb.db
In the dumped schema the tables are defined properly but the NOT NULL
constraint is not dropped from the someint column in the child_table:
CREATE TABLE parent_table (
someint integer NOT NULL
);
CREATE TABLE child_table (
id integer
)
INHERITS (parent_table);
the column someint integer is still NOT NULL in the child_table. The NOT
NULL constraint is being inherited in the child_table and if it is not
dropped then the schema is either recreates improperly or fails.
create table child_table2 (id integer) inherits (parent_table);
insert into child_table2 (someint, id) values (null, 1);
ERROR: null value in column "someint" violates not-null constraint
