Postgresql INSERT FROM SELECT RETURNING ID

sql

prompt-engineering

best-practices

performance

byNikita Barsukov·Dec 22, 2024

In PostgreSQL, use an INSERT INTO...SELECT statement with a RETURNING clause inside a CTE (Common Table Expression) to quickly get back the id:

WITH inserted AS (
  INSERT INTO target_table (col1, col2)
  SELECT col1, col2 FROM source_table  -- Star Trek: The Next Generation, but for data
  RETURNING id
)
SELECT id FROM inserted;

This efficient little query inserts records from source_table into target_table and swiftly fetches the newly minted ids. These ids are now ready for your next mission - er, operation.

Deploying RETURNING within transaction blocks

In your transactions, you can directly catch the newly returned id by assigning it to a local variable. This enables it to be immediately accessible for follow-up commands within the same transaction block:

BEGIN;

WITH inserted AS (
  INSERT INTO clients (name, email)
  SELECT name, email FROM new_clients_temp
  RETURNING id
)
SELECT id INTO my_new_client_id FROM inserted;  -- Congrats! You just caught an id, Pikachu style!

UPDATE orders SET client_id = my_new_client_id WHERE order_id = my_target_order_id; -- Update the Pokedex... I mean, order

COMMIT;

By introducing the RETURNING clause into the transaction and storing its result into a variable, your entire process becomes a sleek one-transaction operation. Efficiency level up: unlocked!

Suspending batch insertions with arrays

When multiple data rows need to be inserted and their new IDs captured, store them in an array or process them immediately:

WITH inserted AS (
  INSERT INTO blogs (title, content)
  SELECT title, content FROM imported_blogs
  RETURNING id
)
SELECT array_agg(id) FROM inserted;  -- Squeezing IDs into an array, like canned sardines. Yum!

With this tweak, you can perform bulk operations and grasp all the fresh identifiers in one swoop, improving overall efficiency and your chances for world domination.

Elevating with function and trigger

To automate data management, you can combine RETURNING id with database functions and triggers. Let PostgreSQL do the work for you!

CREATE OR REPLACE FUNCTION insert_with_trigger() RETURNS TRIGGER AS $$
BEGIN
  NEW.dealer_id := (INSERT INTO dealers (name) VALUES (NEW.dealer_name) RETURNING id); -- Creating dealers like a boss!
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_before_insert
BEFORE INSERT ON vehicles
FOR EACH ROW EXECUTE FUNCTION insert_with_trigger(); -- Vehicle creation, now dealer-friendly!

This ensures each vehicle insert also creates a related dealer entry and autolinks both with the dealer_id. Vehicle-dealer relationships have never been so meticulously managed.

Tricks, traps, and tips

Beware NULLs: Ensure your SELECT statement doesn't introduce unexpected NULL values. Phantom data isn't as fun as it sounds.
Be matchmaker: Column types must match - or heartbreak ensues.
Duplicates? Nope: Avoid unintentionally doubling your insertions. Use the DISTINCT keyword judiciously in your SELECT.
Raw Speed: For larger data sets, optimise the INSERT FROM SELECT RETURNING ID pattern. Use batch operations or consider a clown-car approach, cramming loads of data into small units.