Postgres copy data between tables

Question

I've created table1 with columns a,b,c,d which has data in it. table2 is basically the same as table1 it has different columns order + additional column i.e. a,e,d,b,c with no data.

how can I copy the data from table1 into table2 note that column a is an id and i wish the number will stay the same.

this is what I've already tried:

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

this resulted in column "a" is of type bigint but expression is of type record

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

didn't work either syntax error at or near "select"

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

got the error: INSERT has more target columns than expressions

Answer 1

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

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

Answer 2

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 the INSERT 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