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

Question

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.

Solution 1

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.

Solution 2

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');

Solution 3

Suppose there is already a table and you want to copy all records from this table to another table which is not currently present in the database then following query will do this task for you:

SELECT * into public."NewTable" FROM public."ExistingTable";

Comments

[a_horse_with_no_name]

No need for a copy command: insert into other (col1, col2) select col1, col2 from one

[user2864740]

The time to use copy/dump is when moving data between unconnected databases (using files as an interim). If the tables are within the same database there is no need for this.

[Mohitd23]

Thanks for your reply but I am just curious if is it possible to copy data from one table to another using copy command . If yes the how?

[RyanM]

I just created duplicate tables for an app in a different schema but when I attempt the INSERT INTO mycopy SELECT * FROM mytable approach I get ERROR: column "order" is of type integer but expression is of type text LINE 2: SELECT * FROM django_apps.about_post To be clear, order is an integer in both tables.

[Craig Ringer]

@RyanM New question please and show \d output from psql in both tables

[Noumenon]

@RyanM Same. You can avoid this by using the column names, like INSERT INTO mycopy (a, b) SELECT * FROM mytable, but I was coming here to try to find an alternative to that. My tables have all the same columns, just in a different order. Turns out it's not possible. stackoverflow.com/questions/1267427/…

[mrroot5]

The second option (INCLUDING ALL) was perfect for me with postgres 11.2 and alpine linux 3.9

[Victor Ivanov]

Just be aware that table created with (INCLUDING ALL) will share the sequences with the other table

[a_horse_with_no_name]

Note that the select ... into syntax is deprecated in favor of the standard compliant create table ... as select ...

[malcolm]

there is a good reason to do this...if you have multiple views that depend on a table, and you need to update that entire table...unless there is another way to replace a table that has dependent views without recreating all the views