'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