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.
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
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.