2

I have three tables, which I create with these queries:

create table parents(
    rowid serial primary key,
    display_name varchar,
    unique(display_name)
);

create table clients(
    rowid serial primary key,
    parent int,
    display_name varchar,
    foreign key (parent) references parents(rowid),
    unique(display_name, parent)
);

create table datapoints(
    rowid serial primary key,
    client int,
    val float8,
    foreign key (client) references clients(rowid)
);

I am getting data which contains parent name, client name and some value. Each time I get this data, I want to add rows to the datapoints table. I also want to add rows to the clients and parents table, but only if the data I received has unrecognized names.

For example, I might get this data:

"parent1-client1-123.0"

What I would like to achieve is to process this data (in a query), this way:

Insert a row to the "parents" table, if needed.

Insert a row to the "clients" table with the appropriate parent id (obtained in the previous step?), if needed.

Insert a row to the "datapoints" table with the appropriate client id (obtained in the previous step?).

How can I manage this with queries? The programmer in me wants to write:

  • Insert into parents if needed. Save the id of appropriate parent in a variable "current_parent"
  • Insert into clients if needed, using the "current_parent" variable. Save the id of the appropriate client in a variable "current_client".
  • Insert into datapoints, using the "current_client" variable from the prev. step.

But obviously this is not how sql works, right? And what if, instead of these three "nested" tables I have 5, or 10?(!)

Please help

1 Answer 1

4

That is a trivial application of INSERT ... RETURNING:

INSERT INTO parents (display_name)
VALUES ('papa')
RETURNING rowid;

Then you can use the returned value for the next INSERT.

You can do all three inserts in a single SQL statement using CTEs:

WITH p_ids AS (
   INSERT INTO parents (display_name)
   VALUES ('papa')
   RETURNING rowid
), c_ids AS (
   INSERT INTO clients (parent, display_name)
   SELECT p_ids.rowid, 'murcielago'
   FROM p_ids
   RETURNING rowid
)
INSERT INTO datapoints (client, val)
SELECT c_ids.rowid, 2.71828
FROM c_ids;
2
  • Thank you! Can I combine it with a "only insert if not exists" type of logic?
    – birgersp
    Feb 17, 2022 at 12:29
  • 2
    Run three INSERT INTO ... ON CONFLICT. But I won't spoon-feed you. Feb 17, 2022 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.