Indices, Drop before Loading and Create after Loading

Question

1. How do you drop all indices before loading new data into an existing table, and make new indices after loading the new data ?
2. COPY command is faster than INSERT INTO right ?

Answer 1

Part 1.

Suppose you have a table test

CREATE TABLE public.test (
    bill integer,
    fred text,
    stamp timestamp without time zone
);

Add a UNIQUE CONSTRAINT

test=# ALTER TABLE test
ADD CONSTRAINT fred_ux UNIQUE (fred);
ALTER TABLE

Examine your table:

test=# \d test
                          Table "public.test"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 bill   | integer                     |           |          | 
 fred   | text                        |           |          | 
 stamp  | timestamp without time zone |           |          | 
Indexes:
    "fred_ux" UNIQUE CONSTRAINT, btree (fred)

Now, we can see that our unique constraint fred_ux is there.

To drop it, we simply do:

test=# ALTER TABLE test DROP CONSTRAINT fred_ux;
ALTER TABLE

Luckily, we can chain these - i.e. you can write (details here)

ALTER TABLE xyz DROP CONSTRAINT abc, def, ghi, ......

Examine table test again:

test=# \d test
                          Table "public.test"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 bill   | integer                     |           |          | 
 fred   | text                        |           |          | 
 stamp  | timestamp without time zone |           |          | 

Et voilà! fred_ux is gone!

To restore your indices after whatever your load process is, you simply rerun the commands which created them in the first place. In order to have a record of these commands, then BEFORE you drop your indexes, run the command

./bin/pg_dump -s -t my_table my_schema

and pump the output to a file. In that file, you will have all the information you require to recreate that table, including any indexes, constraints &c.

Part 2.

COPY is way quicker than using INSERTs. However, COPY has one major flaw - if there's even one dodgy record, the entire batch will fail - there's no recovery mechanism. You might want to look at pg_loader - you can find a basic outline here.