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.

Answer 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.