databasepostgresqlinheritance

Ignore inheritance during COPY


Let's say we have a parent and a child table as follows:

CREATE TABLE cities (
    name            text,
    population      float,
    elevation       int     -- in feet
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

Now, when I insert any row on child capitals it is expected to insert the same on the parent table cities as well.

I am using a migration tool, which uses the COPY command to populate data from the source to the target.

The rows are duplicated in the parent table because there are 2 inserts.

  • COPY command of itself
  • COPY command of its children

when it inserts the rows into the child table using COPY, the tables are inserted into the parent table as well.

I want to insert some data into the child table using the COPY command without inserting them into the parent table.

Is there any way to achieve this


Solution

  • The rows are not duplicated. If you insert into capitals, nothing is inserted into cities. But when you SELECT from cities, PostgreSQL will also show the results from capitals. In fact,

    SELECT * FROM cities;
    

    actually performs

    SELECT name, population, elevation
    FROM ONLY cities
    UNION ALL
    SELECT name, population, elevation
    FROM ONLY capitals;
    

    You can use ONLY to avoid that:

    SELECT * FROM ONLY cities;
    

    But if you don't want the default behavior of table inheritance, it might be best not to use the feature at all.