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.

No need for a copy command: `insert into other (col1, col2) select col1, col2 from one` — , Jul 08 '15 at 05:56
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. — user2864740, Jul 08 '15 at 05:58
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? — Mohitd23, Jul 08 '15 at 06:28
...and how can one add a default value for a particular field? Is it possible within the `INSERT` statement? — Gathide, Jul 19 '22 at 09:10
@Gathide you should be able to set the default in the definition of the table. — Tommie Jones, Jul 07 '23 at 18:45

score 287 · Accepted Answer · edited Jan 17 '17 at 17:07

287

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.

edited Jan 17 '17 at 17:07

Flimm

136,138
45
251
267

answered Jul 08 '15 at 05:54

Craig Ringer

307,061
76
688
778

3

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. – RyanM Jul 02 '18 at 23:03
1

@RyanM New question please and show `\d` output from `psql` in both tables – Craig Ringer Jul 04 '18 at 13:15
5

@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. https://stackoverflow.com/questions/1267427/sql-insert-all-records-from-one-table-to-another-table-without-specific-the-col – Noumenon Feb 27 '19 at 18:50
1

The second option (INCLUDING ALL) was perfect for me with postgres 11.2 and alpine linux 3.9 – mrroot5 Mar 03 '19 at 12:15
10

Just be aware that table created with (INCLUDING ALL) will share the sequences with the other table – Victor Ivanov Mar 17 '19 at 11:37
1

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 – malcolm Nov 05 '20 at 14:45
There is a short form syntax: `CREATE TABLE mycopy AS TABLE mytable;` – Killian Huyghe Sep 30 '22 at 03:16
It's worth noting that INCLUDING ALL will not copy Foreign keys, triggers, etc. – Abhishek Dalvi Mar 21 '23 at 16:31

score 18 · Answer 2 · answered Nov 25 '19 at 12:32

18

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

answered Nov 25 '19 at 12:32

Steve Irwin

201
2
5

Manish Jain · Answer 3 · 2019-06-20T13:41:22.967

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";

edited Jun 20 '19 at 13:41

answered Jun 20 '19 at 05:30

Manish Jain

1,197
1
11
32

4

Note that the `select ... into` syntax is deprecated in favor of the standard compliant `create table ... as select ...` – Nov 25 '19 at 12:34

score 1 · Answer 4 · edited Jul 18 '23 at 04:34

You can use the CREATE TABLE AS command.

CREATE TABLE AS — define a new table from the results of a query

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] AS query [ WITH [ NO ] DATA ]

CREATE TABLE mytable_backup AS TABLE mytable;

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

4 Answers4

Linked