0

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
AME
  • 270
  • 1
  • 13

0 Answers0