Postgresql: Sub-select inside insert

Unpacking the sub-select inside an insert

In a sub-select operation, ensure you've got column alignment between the source table and the target table. This provides you with the correct mapping and prevents any possible errors or unintended data associations.

Inserting constants? No problem!

There are times when you want to insert static values alongside dynamic values. That's easily done in PostgreSQL:

-- Warning: Once you go 'Blue', you will never undo!
INSERT INTO widgets (widget_id, widget_name, color)
SELECT DEFAULT, widget_name, 'Blue' FROM source WHERE condition;


In this code snippet, we set the widget_id to DEFAULT to allow auto-increment, and 'Blue' is a constant value added to all records.

Aggregating on the fly

To aggregate data during the insert, you can:

-- "Daily digests - 'Cause nobody has time for hourly updates!"
INSERT INTO sales_summary (sale_date, total_sales)
SELECT TODAY, SUM(amount) FROM sales WHERE sale_date = TODAY;


This summarizes today's sales and inserts it into the sales_summary table.

Multiple rows? Bring it on!

If your sub-select returns multiple rows, PostgreSQL got your back! It'll insert all of them:

-- "You got approval! You got approval! Everyone gets an approval!"
INSERT INTO project (project_name, start_date)
SELECT name, start_date FROM proposals WHERE approved;


This will create a record in the project table for every approved proposal.

When sub-selects may not work

While sub-selects are potent, they may sometimes not be the best choice in scenarios like:

• Performance overhead: For bulky datasets, sub-selects can be slower than the flash, but definitely not as fast as bulk inserts.
• Over-complicated subqueries: If constructing a sub-select feels like assembling a spaceship, it may be better to use Common Table Expressions (CTEs).
• Locking issues: Sub-selects may cause pesky locks on your source tables that you didn't sign up for.

Tips to sub-select like a pro

• Explicitly list target columns to guard yourself against future schema changes or unexpected errors.
• Use aliases to easily identify tables when dealing with multiple tables and nested sub-selects.
• Get faster query execution by indexing source table on columns used in your WHERE clause.