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