How to insert values into a table from a postgres insert select query?
I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int).
Now I want to insert a row into items_ver from items. Is there any short SQL-syntax for doing this?
I have tried with:
INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item_id = 2);
but I get a syntax error:
ERROR: syntax error at or near "select"
LINE 1: INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item...
I now tried:
INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;
It worked better but I got an error:
ERROR: column "item_group" is of type integer but expression is of type
character varying
LINE 1: INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;
This may be because the columns are defined in a different order in the tables. Does the column order matter? I hoped that PostgreSQL would match the column names.
To insert values into a table from a postgres insert select query -
INSERT INTO test_import_two (name, name1, name2)
<strong>(SELECT name, name1, name2 FROM test_import_one WHERE id = 2)</strong>For same table
INSERT INTO test_import_three (id1, name1, name2)
(SELECT 216 ,name1, name2 FROM test_import_three WHERE id = 4)
Answer (1)
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:
- The INSERT INTO employees_backup specifies the target table and the columns where the data will be inserted.
- The SELECT first_name, last_name, department, salary FROM employees specifies the columns to select from the employees table.
- 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;