Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I copy data from one table to another in postgres using copy command

We use copy command to copy data of one table to a file outside database.

Is it possible to copy data of one table to another table using command.

If yes can anyone please share the query.

Or is there any better approach like we can use pg_dump or something like that.

like image 772
Mohitd23 Avatar asked Jul 08 '15 05:07

Mohitd23


People also ask

How do I use Copy command in PostgreSQL?

PSQL \Copy Command for Client-Side ExportTo copy the entire table to a csv file, use \copy. This will copy the contents of a table to the client computer as a csv file. The file will not contain the headers of the table. \copy employees to '/var/lib/postgresql/emp.

How copy data between PostgreSQL databases?

If you really have two distinct PostgreSQL databases, the common way of transferring data from one to another would be to export your tables (with pg_dump -t ) to a file, and import them into the other database (with psql ).

How do I copy data from one table of one database to another table of another database in SQL?

Right-click on the database name, then select "Tasks" > "Export data..." from the object explorer. The SQL Server Import/Export wizard opens; click on "Next". Provide authentication and select the source from which you want to copy the data; click "Next". Specify where to copy the data to; click on "Next".


2 Answers

You cannot easily do that, but there's also no need to do so.

CREATE TABLE mycopy AS SELECT * FROM mytable; 

or

CREATE TABLE mycopy (LIKE mytable INCLUDING ALL);  INSERT INTO mycopy SELECT * FROM mytable; 

If you need to select only some columns or reorder them, you can do this:

INSERT INTO mycopy(colA, colB) SELECT col1, col2 FROM mytable; 

You can also do a selective pg_dump and restore of just the target table.

like image 178
Craig Ringer Avatar answered Sep 21 '22 02:09

Craig Ringer


If the columns are the same (names and datatypes) in both tables then you can use the following

INSERT INTO receivingtable (SELECT * FROM sourcetable WHERE column1='parameter' AND column2='anotherparameter'); 
like image 20
Steve Irwin Avatar answered Sep 21 '22 02:09

Steve Irwin