7

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?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
raizsh
  • 456
  • 1
  • 6
  • 16
  • 1
    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 Jun 28 '17 at 07:51

1 Answers1

6

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)
Mureinik
  • 297,002
  • 52
  • 306
  • 350