1

Given a postgresql 12 database with some table inheritance, and the child table adds no additional columns:

CREATE TABLE parent (
    id integer NOT NULL PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE child () INHERITS (parent);

If I run pg_dump (with pg_dump foo), the tables look like this in the export:

CREATE TABLE public.parent (
    id integer NOT NULL,
    name text NOT NULL
);

CREATE TABLE public.child (
)
INHERITS (public.parent);

If I then detach and reattach the child:

$ psql foo
psql (12.1 (Debian 12.1-2))
Type "help" for help.

foo=# ALTER TABLE child NO INHERIT parent;
ALTER TABLE

foo=# ALTER TABLE child INHERIT parent;
ALTER TABLE

... and try another pg_dump, the export has changed. The child table now explicitly lists the columns that are inherited from the parent:

CREATE TABLE public.parent (
    id integer NOT NULL,
    name text NOT NULL
);

CREATE TABLE public.child (
    id integer,
    name text
)
INHERITS (public.parent);

Is there anything I can do to get pg_dump to output the child table with no explicit columns?

It sounds like a contrived example, but I have a multi-Tb database that with child tables that have been detached and reattached back in the mists of time, and I'd love the schema exports to go back to being as simple (and readable) as possible.

In particular, it'd be great for everyone to see at a glance that the child table adds no additional columns.

1 Answer 1

1

You could mess with the pg_attribute catalog table and set attislocal to FALSE, but modifying the system catalogs is unsupported and dangerous.

In my opinion, that is not warranted for a mere esthetic improvement. The dump works just as fine as it is.

1
  • Manually messing with the catalog is definitely not something I want to do, thanks for the advice! Jan 16, 2020 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.