CODEWITHSUNDEEP
sqlpostgresql
Mefitico
Mefitico

Reputation: 1116

Replacing a table with a copy of another, preserving content and properties in PostgreSQL

I often see these suggestions on how to copy one table into another:

This one should create a table with the same properties (i.e. primary key and constraints), it doesn't actually work in PostgreSQL though:

CREATE TABLE New_Users  LIKE Old_Users;

This one should just copy the content, and some posts (like this one)[https://stackoverflow.com/a/11433568] claim you can do it after the first command.

INSERT INTO New_Users SELECT * FROM Old_Users GROUP BY ID;

In PostgreSQL, it seems like the second command creates the table if it does not exist, and fails if the table is already there, but it indeed does not preserve the properties of the original table.

I'd like to atomically drop a table and create a new one with the same name, but with the contents and properties of another existing table. Something like this:

BEGIN; 
    DROP TABLE New_Users;
    CREATE TABLE New_Users  (LIKE Old_Users);
    INSERT INTO New_Users SELECT * FROM Old_Users;
END;

This should atomically delete the current version of the table, create a skeleton with all properties and finally populate it with the content of the reference table. How can I do this in PostgreSQL?

Upvotes: 0

Views: 39

Answers (1)

nbk
nbk

Reputation: 49385

You can use like with an option

DROP TABLE New_Users;
create table New_Users (like Old_Users including all)

or much faster

TRUNCATE New_Users RESTART IDENTITY;
INSERT INTO New_Users SELECT * FROM Old_Users;

Upvotes: 2

Related Questions