S
S
Sergey Alekseev2019-01-29 13:52:38
PostgreSQL
Sergey Alekseev, 2019-01-29 13:52:38

Postgres, how to create a new table that fully matches another table?

That is, there is a table of cities - with data, indexes, restrictions.
I want to create a new table that fully corresponds to the table of cities. How to do it?
I tried to do it this way:
CREATE TABLE new_city AS (SELECT * FROM city);
In the new table there were only data.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Melkij, 2019-01-29
@serj2000

create table new_city (like city including all);
insert into new_city select * from city;

If conditions allow, then it will be faster to dump, rename and write back.
pg_dump -t city -Fc -Z0 -f reimport.pgdump
alter table city rename to new_city;
pg_restore -f reimport.pgdump
Faster on indexes that build better after the data is written.
And to immediately clone the table - there seems to be no such thing, a very narrow usecase

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question