Postgresql – Transfer row from ‘parent’ to ‘child’ without creating duplicate and without knowing all ‘parent’ columns

duplicationinheritancepostgresqlschema

I want to set up a database with different schemas that correspond to different data-entry systems. There needs to be a “master” (administration) schema with a “parties” table, and multiple other schemas with their own “parties” tables that inherit fields from the master parties table

Here’s a simplified example of the structure:

'PARENT'

CREATE TABLE master.parties
(
  party_key serial NOT NULL,
  name text,
  date_of_birth date,
  phone text,
  CONSTRAINT "PK_master_parties" PRIMARY KEY (party_key)
)

'CHILD'

CREATE TABLE corporate.parties
(
  corp_key integer,
  corp_role text
) INHERITS (master.parties)

I would like to be able to bring down a row that was added to the master.parties table and insert it into any of the parties tables in the other schemas (ie. corporate), without creating a duplicate row in the master table.

This solution I found on another post (https://stackoverflow.com/questions/29509162/postgres-table-inheritance-move-from-parent-to-child-and-vice-versa) was very close to what I am looking for:

WITH deleted AS (
  DELETE FROM ONLY master.parties 
  WHERE party_key = 1
  returning *
)
INSERT INTO corporate.parties (name, date_of_birth, phone, corp_key, corp_role)
SELECT name, date_of_birth, phone, 2, 'President'
from deleted;

However, it seems to me that there should be a better way of adding a row from the 'parent' table to the 'child' table without having to delete the existing row in the 'parent' table.

This solution also poses another, more serious problem: if a change is made to the parties table in the master schema (eg. new column added), I don’t want to have to update all the data-entry systems corresponding to the sub-schemas to include that new column in every INSERT statement. If I were to miss an INSERT statement in code, or if it wasn't possible to immediately update one of the data-entry systems, I’d risk losing the values in that new column of the master.parties table when I try to bring down a row into another schema’s party table (as it is not included in the existing INSERT statement).

Basically what I’m after is to have the ability to add a row to a 'child' table from the 'parent' table, without necessarily knowing all of the columns in the 'parent' table and without creating a duplicate row in the 'parent' table. Is this possible? Should I be reconsidering my DB design?

Any help or suggestions are much appreciated!

Best Answer

No one (or rather very few) uses "table inheritance", like that. That's a half-baked PostgreSQL feature which is used for some internal underpinnings exposed to the user in a fashion likely to confuse them. I'd highly suggest avoiding it. For more information see,

In your case though you can use a single-table hierarchy or self-referencing hierarchy,

CREATE SCHEMA master;
CREATE TABLE master.entities
(
  entity_id       int  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id       int  REFERENCES master.entities,
  entity_name     text,
  entity_data      jsonb
);

INSERT INTO master.entities VALUES
  ( 1, null, 'Master', null),
  ( 2, 1   , 'Corp'  , '{"date_of_birth":"2018-07-05"}');

I would like to be able to bring down a row that was added to the master.parties table and insert it into any of the parties tables in the other schemas (ie. corporate), without creating a duplicate row in the master table.

I'm not 100% sure what you mean here. Essentially the relations are not meta-data, it's data. So you have to have a duplicate row to have one entity under two parents. If you want to minimize duplication of the JSONB though you could outsource that to another table -- though I would probably not bother with it unless you have a desired answer as to how two parties will manage the data-stored in the jsonb.

Querying the above structure is slightly more complex, but you'll get the hang of it, you have to use a recursive CTE. You can find lots of examples of this under ,

WITH RECURSIVE t(id,parents,name, data) AS (
  SELECT entity_id, ARRAY[]::int[], entity_name, entity_data
  FROM master.entities
  WHERE parent_id IS NULL
  UNION ALL
    SELECT e.entity_id, parents||parent_id, e.entity_name, e.entity_data
    FROM t
    INNER JOIN master.entities AS e
      ON e.parent_id = t.id
)
TABLE t;

 id | parents |  name  |              data               
----+---------+--------+---------------------------------
  1 | {}      | Master | 
  2 | {1}     | Corp   | {"date_of_birth": "2018-07-05"}
(2 rows)

Note this schema also permits N-level relationships (you can create an almost infinite amount of children under children -- no additional tables required)

Related Question