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
SELECTstatement should match the columns you specified in the
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,
completed_orders, and you want to copy all completed orders from the
orders table to the
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.