I have got three tables:
CREATE TABLE public.nameattribute
(
"label" character varying
id integer NOT NULL DEFAULT
CONSTRAINT name_pkey PRIMARY KEY (id),
CONSTRAINT "name_label_key" UNIQUE ("label")
)
CREATE TABLE public.objattribute
(
"label" character varying
id integer NOT NULL DEFAULT
CONSTRAINT obj_pkey PRIMARY KEY (id),
CONSTRAINT "obj_label_key" UNIQUE ("label")
)
CREATE TABLE public.valuetable
(
name_id integer NOT NULL,
obj_id integer NOT NULL,
value text
CONSTRAINT name_id_fkey FOREIGN KEY (name_id)
REFERENCES public.nameattribute (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT obj_id_fkey FOREIGN KEY (obj_id)
REFERENCES public.objattribute (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
I want to insert multiple rows in tablevalue. For example:
("name1", "obj1", "value1")
("name1", "obj2", "value2")
("name2", "obj1", "value3")
Results:
nameattribute
| id | label |
|---|---|
| 1 | name1 |
| 2 | name2 |
objattribute
| id | label |
|---|---|
| 1 | obj1 |
| 2 | obj2 |
valuetable
| name_id | obj_id | value |
|---|---|---|
| 1 | 1 | value1 |
| 1 | 2 | value2 |
| 2 | 1 | value3 |
As I found here, I should use something like this: Insert data in 3 tables at a time using Postgres
But unfortunately I could not implement it on my case. This is how I tried:
WITH data(name, obj, value) AS (
VALUES
('name1', 'obj1', 'value1'),
('name1', 'obj2', 'value2'),
('name2', 'obj1', 'value3')
)
, ins1 AS (
INSERT INTO valuetable (value)
SELECT value
FROM data
RETURNING <> <--- I guess here I should return with the name and obj id somehow
)
, ins2 AS (
INSERT INTO nameattribute (id, label)
SELECT ins1.name_id, d.name
FROM data d
JOIN ins1 USING (<>) <--- Don't know what goes here
RETURNING id
)
INSERT INTO objattribute (id, label)
SELECT ins1.obj_id, d.obj
FROM data d
JOIN ins1 USING (<>) <--- Don't know what goes here