0

I'm currently working with PostgresSQL 13.3 and I thought I could have a file with something like this:

INSERT INTO points (zone_id, element_id, position_id) VALUES
    (SELECT zones._id, elements._id, positions._id FROM zones, elements, positions WHERE zones.name = 'A' AND elements.name = 'abc' AND positions.name = 'xyz'),
    (SELECT zones._id, elements._id, positions._id FROM zones, elements, positions WHERE zones.name = 'B' AND elements.name = 'bcd' AND positions.name = 'yza');

That doesn't work but I can bulk-insert using:

INSERT INTO points (zone_id, element_id, position_id) SELECT zones._id, elements._id, positions._id FROM zones, elements, positions WHERE zones.name = 'A' AND elements.name = 'abc' AND positions.name = 'xyz';
INSERT INTO points (zone_id, element_id, position_id) SELECT zones._id, elements._id, positions._id FROM zones, elements, positions WHERE zones.name = 'B' AND elements.name = 'bcd' AND positions.name = 'yza';

I have a little more than 20,000 records and instead of executing thousands of sequential INSERTs each containing one SELECT, I was wondering if there was a way to execute a single INSERT containing all those SELECTs?

One thing that bothers me with on INSERT per SELECT is that I'm currently seeing the result of each INSERT independantly and if I do a SELECT COUNT(*) FROM points; at the end of the execution, I notice I'm missing one row in the database and I have no idea which one it is.

Improve this question
3
  • @a_horse_with_no_name, this join table is basically defining a three-level structure, i.e. zones containing elements containing positions.
    – Tolga
    Jun 21, 2021 at 13:55
  • An how do you know which elements belongs to which zone and which position belongs to which element if you don't properly join those three tables?
    – user1822
    Jun 21, 2021 at 13:56
  • @a_horse_with_no_name, those relationships are predefined by business rules and that's the role of this join table, but maybe I don't see the issue you're trying to make me be aware of here?
    – Tolga
    Jun 21, 2021 at 14:03

1 Answer 1

Reset to default
2

Get rid of the values clause and use an IN clause:

INSERT INTO points (zone_id, element_id, position_id) VALUES
SELECT zones._id, elements._id, positions._id 
FROM zones, elements, positions
WHERE (zones.name, elements.name, positions.name) IN ( ('A','abc','xyz'),
                                                       ('B','bcd','yta') );

But the cross join between the three tables looks really wrong to me.

Improve this answer

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