Re: pg_dump problem with dropped NOT NULL on child table

On 01/13/2016 11:38 AM, Karsten Hilbert wrote:
> Please consider the following:
>
> - Debian
> - server is PG 9.4.5
> - client (psql/pg_dump/libpq5) is 9.5
>
> create table parent (
> not_null_in_parent integer not null
> );
>
> create table child() inherits (parent);
> alter table child
> alter column not_null_in_parent
> drop not null
> ;
>

>
> --------------------------------------------------
>
> Is this a bug or am I doing things I shouldn't hope work ?

The latter if I am following the below correctly:

http://www.postgresql.org/docs/9.5/static/ddl-inherit.html

"All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints
(unique, primary key, and foreign key constraints) are not inherited."

>
> I noticed this during a recent 9.4 -> 9.5 pg_upgradecluster
> attempt with actual data in "child" violating-upon-restore
> the newly created NOT NULL constraint on "child" when COPYing.
>
> Thanks,
> Karsten
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com