How to create a copy of table in PostgreSQL?

Question

I want to save a copy of specific table with it's data. All I care is the structure and data. I don't want to copy the keys nor constrains.

I read this answer Copy table structure into new table but it's not quite what I need.

How it can be done?

create table copy_table as select * from original table ?.. — Vao Tsun
– Vao Tsun, Commented Apr 6, 2017 at 7:35

oerpli · Accepted Answer · 2018-07-03 12:38:56Z

58

You are probably looking for CREATE TABLE AS SELECT, e.g.:

CREATE TABLE copy_table AS SELECT * FROM original_table;

edited Jul 3, 2018 at 12:38

oerpli

3504 silver badges16 bronze badges

answered Apr 6, 2017 at 7:36

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

José Over a year ago

"original table" ---> original_table ;-)

Simon D Over a year ago

CREATE TABLE copy_table AS TABLE original_table; - shorthand version

Vao Tsun Over a year ago

yes, but TABLE is much less flexible than SELECT - I would prefer answering it with select, as you can add any where or whatever to a statement, not just limit or what the possible option list it

Michael Rush Over a year ago

and if you just want the table structure, without the data you can add WHERE 1=0

Collectives™ on Stack Overflow

How to create a copy of table in PostgreSQL?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related