Postgresql – How to Improve pg_dump Exports for Inherited Tables

inheritancepostgresql

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.

Best Answer

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.

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

Postgresql – How to find table name for each row result

There must be some kind of misunderstanding. tableoid is a system column that's available for any regular table in any version of Postgres since at least v7.3. Per documentation:

Every table has several system columns that are implicitly defined by the system.

tableoid is one of them. The solution I provided under the questions you refer to is also suggested in the manual here. And it works. I have been using it for years in various Postgres versions.

Either you are not using Postgres, or template is not a table. Is it a VIEW maybe? If you define a VIEW like:

CREATE VIEW template AS
SELECT * FROM some_table;

Then system columns are not included by default.

What do you get for

SELECT relnamespace, relname, relkind
FROM   pg_class
WHERE  relname = 'template';

Is relkind 'r', 'v' or something else?
Or do you get multiple rows? Then consider this:

Why does a PostgreSQL SELECT query return different results when a schema name is specified?

Best Answer

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Postgresql – How to find table name for each row result

Related Question