PostgreSQL Insert Into Table Select * From Another Table

In this PostgreSQL tutorial, we will know how PostgreSQL Insert Into Table Select * From Another Table with syntax and examples.

PostgreSQL Insert Into Table Select * From Another Table

Consider a situation where you need to insert the data from one table to another table, “What will you do?”. You will use the statement “INSERT INTO table1 SELECT * FROM table2” that takes the data from table2 and insert it into table1.

It consists of two statements “INSERT INTO” and “SELECT”, It first selects the data from the table2 and inserts the selected or copied data into the table1.

The syntax is given below.

INSERT INTO table_name1 (column_name1, column_name2, ...)
SELECT column_name1, column_name2, ...
FROM table_name2;

• INSERT INTO table_name1 (column_name1, column_name2, …): The table_name1 is a target table where the data is inserted, and this data comes from another table name table_name2.
• SELECT column_name1, column_name2, … FROM table_name2: This statement selects or copies the data from the table_name2.

If you want to insert all the data from table_name2 into table_name1, then use this syntax.

INSERT INTO table_name1
SELECT * FROM table_name2;

For example, we have a table called ’employees’ with columns first_name, last_name, department_id, and salary that is shown below.

Create a new table named “employees_salary” with columns first_name, last_name, and salary using the below command.

CREATE TABLE employees_salary (
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  salary NUMERIC(10,2)
);

Now select the data from the table ’employees’ to the newly created table ’employees_salary’. Here target and source table is ’employees’ and ’employees_salary’ respectively.

INSERT INTO employees_salary(first_name, last_name, salary)
SELECT first_name, last_name, salary FROM employees;

SELECT * FROM employees_salary;

The above query inserts the data from the table ’employees’ to the corresponding columns of the table ’employees_salary’.This means the data from columns ‘first_name’, ‘last_name’, and ‘salary’ of the table employees is copied to the columns ‘first_name’, ‘last_name’, and salary of the table ’employees_salary’.

Next, we will see how to insert the data from one table to another while creating the new table. Actually, we are going to use the command that first creates a table and then inserts the data from another table into the created table.

CREATE TABLE emplyees_salary as 
SELECT first_name, last_name, salary FROM employees
    WHERE salary > 5500;

SELECT * FROM emplyees_salary;

The above query created a new table named ’emplyees_salary’ with columns ‘first_name’, ‘last_name’, and ‘salary’ from the table ’employees’ where the employee salary is greater than $5500.Then get all the data from the table ’emplyees_salary’

Conclusion

In this PostgreSQL, we have learned how to copy data from specific columns of the table to another table. Also learned about inserting the data from another table while creating a new table.

You may like to read:

• How to Drop All Tables in PostgreSQL
• How to create user in PostgreSQL
• How to ALTER TABLE in PostgreSQL