Specify the column names you are inserting, but do not usevalues when defining the select.

insert into table2(a,d,b,c) select a, d, b, c  from table1

You can copy data between tables in postgreSQL by using:

INSERT INTO [Tablename]([columns]) SELECT [Columns] FROM [Table to copy form];

Which in your case would look like:

INSERT INTO table2(a,b,c,d) SELECT a,b,c,d FROM table1;

You can also easily create an empty table with the same table structure so that copying to it is easy, by using the command:

CREATE TABLE [New Table] AS [Old Table] WITH NO DATA;

And then running theINSERT command from before.

If you simply want an identical copy you can run:CREATE TABLE [New Table] as [Old Table];

You can read more about copying data between tables in this article which I wrote for dataschool:https://dataschool.com/learn/copying-data-between-tables

You can read more about the insert command in the documentation here:https://www.postgresql.org/docs/9.2/sql-insert.html

The problem with the first solution is actually the parentheses. If you do:

insert into table2 select a,d,b,c from table1

it's going to work, giventable2 has the same number of columns in the right order.

This happens because in Postgres if you do

select (1, 2), 3

it means you are selecting one field of type record and values 1 and 2 inside of it and one field of type int with value 3.

• sql
• postgresql