0

I have 2 existing tables: ‘projects’ and ‘units’.The two tables are connected through the project id. In the table 'projects' the column is called ‘id’ and generates every time a new project is inserted. In the table 'units' the same id is called ‘projekt_id’.

I now want to add new projects from a third table that is called ‘temp_units’. That table also has information that is relevant for the table ‘units’. So for every row in the ‘temp_units’ table I want to create a new row in the ‘projects’ table and also a new row in the ‘units’ table. Those two rows should be connected through the projects id.

I tried the following query but I get the error: more than one row returned by a subquery used as an expression.

Then I read somewhere about adding Limit = 1, so I added that to the query below (it is shown as a comment there).
With Limit = 1 the code worked but all of the inserted data in the ‘units’ table is connected to the first project created in the 'projects' table.

At the moment it is like this:

projects

id
--
1
2
3

Units

Id | projekt_id 
---------------
 1 | 1 
 2 | 1 
 3 | 1 

But I need it like this:

projects

id
--
1
2
3

Units

Id | projekt_id 
---------------
 1 | 1 
 2 | 2 
 3 | 3

I am using PostgreSQL 9.5.19. I would really appreciate any help

WITH insert_projekte AS
    (
    INSERT INTO projects (name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at)
    SELECT name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at  
    FROM temp_units
    RETURNING ID
    )

INSERT INTO units (kgnr, ez, projekt_id, creator_id, editor_id, inserted_at, updated_at)
SELECT t.kg_ez, t.ez, (SELECT id from insert_projekte/*LIMIT = 1*/), t.creator_id, t.editor_id, t.inserted_at, t.updated_at
FROM temp_units t
RETURNING ID
6
  • In the second INSERT, you need to join the CTE results with the temp_units table. This probably requires more RETURNING columns.
    – CL.
    Aug 16, 2020 at 13:08
  • But how does that work? I don't have an id in the temp_units table to join it with.
    – Malks
    Aug 16, 2020 at 13:53
  • Is there really no mechanism at all to identify rows in temp_units? Is it possible that two rows are exactly the same?
    – CL.
    Aug 16, 2020 at 13:58
  • I have to say I created the ‘temp_units’ table before out of a query, so I will add a new column with an id to that table. But after doing that I am still not quite sure how to fix it. Do I need more RETRUNING columns in the first or second INSERT?
    – Malks
    Aug 16, 2020 at 14:07
  • I have tried it with JOIN insert_projekte on t.id = insert_projekte.id on the second INSERT. The query works but the the created projects still don't have the units data connected.
    – Malks
    Aug 16, 2020 at 14:24

1 Answer 1

0

So I figured it out eventually

        WITH insert_projekte AS
            (
            INSERT INTO projects (name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at)
            SELECT name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at  
            FROM temp_units
            RETURNING ID, name
            )
                        
INSERT INTO units (kgnr, ez, projekt_id, creator_id, editor_id, inserted_at, updated_at)
    SELECT t.kg_ez, t.ez, insert_projekte.id, t.creator_id, t.editor_id, t.inserted_at, t.updated_at
    FROM temp_units t
    JOIN insert_projekte on
    insert_projekte.name = t.name
    RETURNING ID

I always left the subquery in the second INSERT so it never worked before.
Thank you @CL. for the helpful suggestions.

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.