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.klaver@aklaver.com