How to insert values into a table from a postgres insert select query?

In PostgreSQL, you can insert values into a table using an INSERT ... SELECT statement. This approach allows you to select data from one or more tables and insert it into another table. Here's the general syntax for the INSERT ... SELECT statement:

INSERT INTO target_table (column1, column2, column3, ...)
SELECT expression1, expression2, expression3, ...
FROM source_table
WHERE condition;

Let's break down each part of the syntax:

• target_table: The table where you want to insert the data.
  
• column1, column2, column3, ...: The columns in the target table into which you want to insert the data. The number of columns must match the number of expressions in the SELECT statement.
  
• expression1, expression2, expression3, ...: The expressions or columns from the SELECT statement that provide the values to be inserted.
  
• source_table: The table from which you are selecting the data.
  
• condition: An optional condition to filter the rows from the source table.

Example

Assume we have two tables: employees and employees_backup. The employees table contains the current employee data, and employees_backup is where we want to insert the data from employees.

employees Table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL
);

employees_backup Table

CREATE TABLE employees_backup (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL
);

Insert Data into employees_backup from employees
INSERT INTO employees_backup (first_name, last_name, department, salary)
SELECT first_name, last_name, department, salary
FROM employees
WHERE department = 'Engineering';

In this example:

1. The INSERT INTO employees_backup specifies the target table and the columns where the data will be inserted.
   
2. The SELECT first_name, last_name, department, salary FROM employees specifies the columns to select from the employees table.
   
3. The WHERE department = 'Engineering' condition filters the rows to be inserted, so only employees in the 'Engineering' department are selected and inserted into employees_backup.
   

Using All Columns

If you want to insert all columns from the source table into the target table and both tables have the same structure, you can simplify the statement by omitting the column lists:

INSERT INTO employees_backup
SELECT *
FROM employees
WHERE department = 'Engineering';

Handling Duplicate Keys

If you want to handle potential conflicts (e.g., duplicate keys), you can use ON CONFLICT:

INSERT INTO employees_backup (id, first_name, last_name, department, salary)
SELECT id, first_name, last_name, department, salary
FROM employees
WHERE department = 'Engineering'
ON CONFLICT (id) DO NOTHING;