PSQL Insert select - Design Then Code

In PostgreSQL (often referred to as “psql”), the INSERT INTO ... SELECT statement is used to insert data into a table from the result of a SELECT query. This statement allows you to combine the insertion of data into a table with the retrieval of data from one or more other tables or queries. It’s a powerful way to copy data from one table to another or to populate a table with the results of a query.

Here’s the basic syntax of the INSERT INTO ... SELECT statement:

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition; -- Optional

target_table: The name of the table where you want to insert data.
column1, column2, ...: The columns in the target table that you want to insert data into. You should list the columns explicitly if you’re not inserting data into all columns.
source_column1, source_column2, ...: The columns from the source table or query whose data you want to insert into the target table. The number and order of columns in the SELECT statement should match the columns you specified in the INSERT INTO clause.
source_table: The name of the source table or a subquery from which you want to retrieve data.
condition: An optional condition that filters the rows from the source table that will be inserted into the target table. If omitted, all rows from the source table will be inserted.

Here’s an example:

Suppose you have two tables, orders and completed_orders, and you want to copy all completed orders from the orders table to the completed_orders table:

INSERT INTO completed_orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'completed';

This statement will insert rows into the completed_orders table, selecting only those rows from the orders table where the status is ‘completed’.

Make sure to properly structure your query, ensuring that the column data types and order match between the source and target tables.