Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties? I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes.

My question is, can I do this without encountering any major difficulties? And if I change this, do I need to rebuild any indexes on those fields?

Will there be any space gains from moving in this direction?

Case-insensitive queries are not necessary on these columns.

I have counts against this table that are predicated on 2 columns, and these counts are taking over an hour. The table has 60 columns.

I am using Postgres 10.6.

I'm primarily interested in whether indexes need to be rebuilt if they contain a column that has changed from CITEXT to VARCHAR.
Will there be any space gains from moving in this direction?


No. citext and text (or varchar) occupy the same space on disk and in RAM.


  Case-insensitive queries are not necessary on these columns.


Then there is no point to use citext.


  I have counts against this table that are predicated on 2 columns, and these counts are taking over an hour. The table has 60 columns.


count() is not affected by citext vs. text (or varchar) at all. "60 columns" might be something to look into. No problem if you actually need all of them - but do you? And are you using proper data types etc.


  I'm primarily interested in whether indexes need to be rebuilt if they contain a column that has changed from CITEXT to VARCHAR.


All indexes involving any of the affected columns have to be rebuilt. (Drop index, change type, recreate index.)

Personally, I stay away from citext after mixed experiences.