'PostgreSQL - INSERT with a nested SELECT, whose WHERE clause depends on value being inserted
Given a table:
CREATE TABLE items(
item_id BIGINT,
item_val BIGINT,
interval_id BIGINT
);
And a table:
CREATE TABLE item_metadata(
item_id BIGINT,
metadata TEXT,
current_interval_id BIGINT
);
Where the item_id is a reference to the one in items and current_interval_id changes dynamically based on a trigger (and a history is kept in a different table - not important for this question).
I want to insert data into the items table, based on what the current_interval_id is for the given item_id.
So a simple solution would look like this:
INSERT INTO items(item_id, item_val, interval_id) VALUES
(5, 15615, (SELECT current_interval_id from item_metadata WHERE item_id = 5));
However, as you imagine, it will get messy when inserting an array. I currently insert into my table, save for the interval_id, a bit like this (I found this to be the best insert performance by far) The %s will of course get substituted by a comma-separated list of values.:
INSERT INTO items (item_id, item_val) SELECT unnest(array[%1])::BIGINT, unnest(array[%2])::BIGINT);
I would like to do a join-like statement for the insert of interval_id, is this possible? A draft of what it could look like:
INSERT INTO items (item_id, item_val, interval_id)
SELECT unnest(array[%1])::BIGINT,
unnest(array[%2])::BIGINT),
SELECT current_interval_id FROM item_metadata WHERE item_metadata.item_id = <insert_set>.item_id;
Note that I cannot use a trigger to do this, as I am using TimescaleDB on top of PostgreSQL and items is a distributed hypertable, whereas item_metadata is not.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
