3

Given a PostgreSQL 9.4 schema with 3 related tables:

CREATE TABLE a (
  id BIGSERIAL PRIMARY KEY,
  a_name VARCHAR(80),
  a_field INTEGER
);
CREATE TABLE b (
  id BIGSERIAL PRIMARY KEY,
  b_name VARCHAR(80),
  b_field INTEGER,
  a_id BIGINT REFERENCES a ON DELETE CASCADE
);
CREATE TABLE c (
  id BIGSERIAL PRIMARY KEY,
  c_name VARCHAR(80),
  c_field INTEGER,
  b_id BIGINT REFERENCES b ON DELETE CASCADE
);

And a structure of application data of the form:

{
  "an10,10":{
    "bn10,10",
    "bn11,11":{
      "cn10,11",
      "cn11,11",
    },
    "bn12,12",
    "bn13,13":{
      "cn12,12"
    },
  "an15,15",
  "an20,20":{
    "bn20,20":{
      "cn20,21",
      "cn21,21",
    },
    "bn21,21":{
      "cn23,23"
    }
}

The id for each table references the parent in the structure above.

We could insert one record-set using a data-modifying CTE like this:

WITH gp AS (
  INSERT into a (a_name, a_field)
  SELECT 'an10',10
  RETURNING id),
p AS (
  INSERT into b (b_name, b_field, a_id)
  SELECT 'bn11',11, gp.id from gp
  RETURNING id)
INSERT into C (c_name, c_field, b_id)
  SELECT 'cn10',10, p.id from p;

SQL Fiddle

But extending that to the full data structure is tripping me up. What is the most efficient way to insert all of the data?

And really, an upsert is what would be desired (a.a_name as the key, everything else would be upserted). But I thought that might be too much. I was just going to bulk-insert and then cascade delete the old record, using a TOP query to prevent the duplicates from showing up until they could be removed.

It's really structured data all bound to table a records. Would a JSON type be a better idea? The data will always be bulk-upserted through table a, but be queried read-only against tables b and c directly, independent of table a (except for the TOP query if we insert instead of upsert). So I thought providing a normalized form was worth a shot.

  • It is an old thread but did you find any solution? We are facing exactly the same issue.. – orhankutlu Oct 10 '17 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.