Renaming postgres table will drop existing indexes?

Question

I am working on an ETL where we get data from hive and dump it to Postgres. Just to ensure the data is not corrupt I first store the data in a temp table (created as the main table with all the indexes and constraints) and if the data is validated copy it to the main table. But it has been taking to long as the data is huge. Once the data is validated I am now thinking of dropping the main table and then renaming the temp table to the main table.

Will renaming a table in Postgres drop the indexes,constraints and defaults defined on it?

Another approach you could take is to keep two tables and use views (I don't think PostgreSQL supports synonyms) as pointers to the correct one for temporary data and the correct one for live data. — David Aldridge
– David Aldridge, Commented Jun 28, 2017 at 7:51

Mureinik · Accepted Answer · 2017-06-28 07:03:51Z

8

It a word - no, it will not drop any indexes, constraints or defaults. Here's a quick demo:

db=> CREATE TABLE mytab (
id INT PRIMARY KEY,
col_uniq INT UNIQUE,
col_not_null INT NOT NULL DEFAULT 123
);
CREATE TABLE
db=> \d mytab
             Table "public.mytab"
    Column    |  Type   |      Modifiers       
--------------+---------+----------------------
 id           | integer | not null
 col_uniq     | integer | 
 col_not_null | integer | not null default 123
Indexes:
    "mytab_pkey" PRIMARY KEY, btree (id)
    "mytab_col_uniq_key" UNIQUE CONSTRAINT, btree (col_uniq)

db=> ALTER TABLE mytab RENAME TO mytab_renamed;
ALTER TABLE
db=> \d mytab_renamed
         Table "public.mytab_renamed"
    Column    |  Type   |      Modifiers       
--------------+---------+----------------------
 id           | integer | not null
 col_uniq     | integer | 
 col_not_null | integer | not null default 123
Indexes:
    "mytab_pkey" PRIMARY KEY, btree (id)
    "mytab_col_uniq_key" UNIQUE CONSTRAINT, btree (col_uniq)

answered Jun 28, 2017 at 7:03

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

1 Comment

Reinsbrain Over a year ago

what about triggers? how might they be affected with a rename of table?

Collectives™ on Stack Overflow

Renaming postgres table will drop existing indexes?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related