2

I'm adding one column in table and after that I'm dropping it too. I found that dropping the column will just hide but not remove it.

Because I repeatedly add and remove columns, I soon get this error:

ERROR: tables can have at most 1600 columns

I know that one solution is to drop the table and recreate it, but it will incur overhead, so it's not suitable for my situation.

How else can I avoid this error?

Toby Speight
  • 27,591
  • 48
  • 66
  • 103
Abhijeet Gulve
  • 799
  • 1
  • 9
  • 23
  • 1
    Dropping a column will not actual be removed. You should do VACUUM and then ANALYZE on the table to remove it permanently – Anuraag Veerapaneni Jul 25 '17 at 06:47
  • yes I have done that Thanks.But still it will be costly operation right ? – Abhijeet Gulve Jul 25 '17 at 06:52
  • Yes, it is a costly operation and that's why it better to do that once a day. If DML operations are happening more on a table then its better to do VACUUM and ANALYZE at least once a day. It will be useful for the query plan to build a better plan – Anuraag Veerapaneni Jul 25 '17 at 07:00
  • Possible duplicate of [Dropping column in Postgres on a large dataset](https://stackoverflow.com/questions/15699989/dropping-column-in-postgres-on-a-large-dataset) – Nick Jul 25 '17 at 12:53
  • A duplicated questions, see https://stackoverflow.com/questions/15699989/dropping-column-in-postgres-on-a-large-dataset, Pavel Stehule's answer is good there. – Nick Jul 25 '17 at 12:53
  • 2
    Perhaps the temporarily added column should not be part of the table, but instead on a temporary table that can be joined on this table's primary key? – Toby Speight Jul 25 '17 at 14:25

1 Answers1

1

Dropping a column will not actual be removed. You should do VACUUM and then ANALYZE on the table to remove it permanently.

It is a costly operation and that's why it better to do that once a day. If DML operations are happening more on a table then its better to do VACUUM and ANALYZE at least once a day. It will be useful for the query plan to build a better plan

Anuraag Veerapaneni
  • 639
  • 4
  • 15
  • 1
    * `VACUUM FULL`, not a regular `VACUUM`. Also, `ANALYZE` can be included to this single command: `VACUUM FULL ANALYZE;`. And to see more details: `VACUUM FULL VERBOSE ANALYZE;`. – Nick Jul 25 '17 at 12:44
  • Also, remarks on doing `VACUUM` "once per day" are not really relevant -- there is a special process, `autovacuum`, which makes it (both `VACUUM` and `ANALYZE`) for you automatically. And regular `VACUUM` is not that heavy operation, unlike `VACUUM FULL`, which physically reorganizes the table. – Nick Jul 25 '17 at 12:46
  • Thanks for your suggestion :) – Abhijeet Gulve Jul 25 '17 at 17:03
  • 2
    `VACUUM FULL` reclaims the space used by a dropped column, but it does **not** remove the column definition; see here: http://rextester.com/UXIN10960 . As far as I know, recreating the table (either manually, or via `pg_dump` & restore) is the only way to reset this 1600-column limit. – Nick Barnes Jul 25 '17 at 21:20