0

We need to change 206 fields in multiple tables to data_type citext (extension). I've created the extension and manually changed a column to see what it should look like in the schema. The data_type needs to be USER-DEFINED and column_default needs to be ''::citext however I can't get this to work.

ALTER TABLE {tablename} ALTER COLUMN data_type USER-DEFINED;

ALTER TABLE {tablename} ALTER COLUMN column_default CONCAT('''','''','::citext');

Update 6/15 I was able to successfully update the type with

ALTER TABLE {table_name} ALTER COLUMN {column_name} TYPE citext;

However, I still have a concern that when looking at the properties of the column, under the constraints tab, the Default value is ''::bpchar and not ''::citext like the manuallly change columns.

enter image description here

Travis
  • 31
  • 3
  • it would be good when you add table definiton of the actual column and and the usertype as well and finally wanted result, from that i can't even guess what you want to achieve – nbk Jun 14 '23 at 14:44
  • 2
    That should be *ALTER TABLE {tablename} ALTER COLUMN {column_name} citext;*. Citext **is the user_defined type** but once created by installing the extension it is used just like any other data type declaration. See the documentation [citext](https://www.postgresql.org/docs/current/citext.html#id-1.11.7.19.7). – Belayer Jun 14 '23 at 15:49
  • Thank you both. Belayer - I've tried this multiple times and it won't accept this change. ALTER TABLE {table_name} ALTER COLUMN {column_name} citext; – Travis Jun 15 '23 at 15:40
  • Sorry, I missed the key word in the alter command. Try *ALTER TABLE {tablename} ALTER COLUMN {column_name} type citext;*. – Belayer Jun 15 '23 at 18:17

0 Answers0