12

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

Improve this question
1

3 Answers 3

Reset to default
24

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Is this a good solution from performance perspective in case of >1mln rows?
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

Improve this answer

Comments

-1

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, given table2 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.

Improve this answer

3 Comments

The OP specifically mentioned that the column order differs between the two tables. Inserting without specifying the columns is not a safe practice: it's analogous to using SELECT * and assuming column order. The most insidious aspect of using these unsafe practices is that data can be silently corrupted when mismatched columns are of compatible types.
@JohnH What? The OP tried a few solutions on their own and I simply explained why the first one didn't work and the role of parenthesis in Postgres. If you read my answer properly you'll see that's exactly what I wrote.
The issue is that you only provided a partial answer. Although you explained the problem caused by the enclosing parenthesis, you did not address the issue of differing column order between the two tables. Inserting without a column list for the target table might not raise an exception; however, it doesn't guarantee that the values will be put into the correct columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.