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, Apr 06 '17 at 07:35

score 36 · Accepted Answer · edited Jul 03 '18 at 12:38

36

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

CREATE TABLE copy_table AS SELECT * FROM original_table;

edited Jul 03 '18 at 12:38

oerpli

answered Apr 06 '17 at 07:36

Vao Tsun

"original table" ---> original_table ;-) – José May 28 '18 at 16:40
9

`CREATE TABLE copy_table AS TABLE original_table;` - shorthand version – Simon D Apr 04 '19 at 03:41
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 – Vao Tsun Apr 08 '19 at 13:52

1 Answers1