CODEWITHSUNDEEP
postgresqlinheritancerelationship
Denis Khvorostin
Denis Khvorostin

Reputation: 867

Inheritance and Relationship in PostgreSQL

I have some problems with SQL-code.

Sequences and tables creation, some data inserting in: 

CREATE SEQUENCE tmp_id_places START 1;
CREATE SEQUENCE tmp_id_books START 1;

CREATE TABLE tmp_places (
  id int PRIMARY KEY DEFAULT nextval('tmp_id_places'),
  name text
);

CREATE TABLE tmp_cities (population int) INHERITS (tmp_places);
CREATE TABLE tmp_rivers (lenght int) INHERITS (tmp_places);

INSERT INTO tmp_cities (name, population) VALUES
  ('Moscow', 15),
  ('St. Petersburg', 9);

INSERT INTO tmp_rivers (name, lenght) VALUES
  ('Volga', 115),
  ('Angara', 319);

CREATE TABLE tmp_books (
  id int PRIMARY KEY DEFAULT nextval('tmp_id_books'),
  id_place int REFERENCES tmp_places(id),
  title text
);

Вut this code make an error:

INSERT INTO tmp_books (title, id_place) VALUES
  ('Some book about Moscow', 1),
  ('Another book about Angara', 4);

Table tmp_books contain information about places. But I can't insert data in it, because there aren't any data in master-table tmp_places (all data in child-tables).

So can this been resolved anyway?

Upvotes: 3

Views: 1746

Answers (3)

user731136
user731136

Reputation:

In PostgreSQL you shouldn't create a foreign key to parent table because, as you just find out, this table acts almost as a view instead of a table (since actual data are in their respective children). For now only be solved by triggers.

You can see an example of this "type of triggers".

Upvotes: 1

vyegorov
vyegorov

Reputation: 22925

Take a closer look at this section in the PostrgeSQL docs. If you will insert data into the child table, then data will be found only in the child table. On the other hand, inserting into the master table makes new rows visible in all the child tables also. So you have to always work on the master tables at first hand.

I've been working with inheritance a while ago and also faced the same problem. I ended up with the following:

  1. INSERT a new entry into the tmp_places;
  2. UPDATE extra fields, say, in tmp_cities with their respective values.

Back in 7.4 times I had to create a set of functions for such activities. Now it is possible to use the RETURNING clause of INSERT statement and CTEs with UPDATE (also on SQL Fiddle): 

WITH theid AS (
  INSERT INTO tmp_places (name) VALUES ('Moscow') RETURNING id
)
UPDATE tmp_cities tc SET population = 15
  FROM theid
 WHERE tc.id = theid.id;

You should also be careful with constraints, as not all of them are inherited.

Upvotes: 4

Edmon
Edmon

Reputation: 4872

Denis,

Inheritance does not propagate in INSERT and COPY statements in Postgres.

Upvotes: 2

Related Questions