CODEWITHSUNDEEP
sqlpostgresql
liv a
liv a

Reputation: 3340

Postgres copy data between tables

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

Upvotes: 12

Views: 20929

Answers (3)

lucasls
lucasls

Reputation: 1640

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.

Upvotes: -1

Matthew Layne
Matthew Layne

Reputation: 131

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

Upvotes: 2

Rob Di Marco
Rob Di Marco

Reputation: 44952

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

Upvotes: 24

Related Questions