How to Copy Data from One Table to Another in Postgres Using Copy Command

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

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.

Copy a table from one database to another in Postgres

Extract the table and pipe it directly to the target database:

pg_dump -t table_to_copy source_db | psql target_db

Note: If the other database already has the table set up, you should use the -a flag to import data only, else you may see weird errors like "Out of memory":

pg_dump -a -t table_to_copy source_db | psql target_db

Postgres copy data between tables

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

Copy data from one table to another - Ignore duplicates Postgresql

Assuming id is your primary key, and table structures are identical(both table has common columns as number of columns and data type respectively), use not exists :

insert into TableB
select * 
  from TableA a 
 where not exists ( select 0 from TableB b where b.id = a.id )

---

Related Topics