Convert varchar to text in PostgreSQL

If you work with PostgreSQL, you will get into the situation when you want to change the data type for the text fields in your tables. This article is about simple way how to convert VARCHAR data type to TEXT data type.

The simple command to alter a column in the database is with TEXT data type looks like this:

ALTER TABLE table_name ALTER COLUMN column_name TYPE text;

The table with table_name alters column with colum_name for TEXT type. That is all you need to do to modify the column data type. The question is, what will happen with data if the table is not empty? Frankly, nothing. TEXT is the data type of unlimited length; therefore, it will adjust to the maximum length of stored data.

Let’s imagine we have a table companies in our project where we store all the information about companies. From the start, we have specified very precisely which field will have what length. We used VARCHAR with a fixed length. For example name of the company could be empty or have 128 characters, email 32 characters, etc. Soon we realize this length limitation is useless. Companies can have very long names; the email address can be longer than 32 characters, and so on and on.

This is an example how you can make multiple alterations to our companies table:

ALTER TABLE project.companies
  ALTER COLUMN name TYPE TEXT;
ALTER TABLE project.companies
  ALTER COLUMN street TYPE TEXT;
ALTER TABLE project.companies
  ALTER COLUMN city TYPE TEXT;
ALTER TABLE project.companies
  ALTER COLUMN post_code TYPE TEXT;
ALTER TABLE project.companies
  ALTER COLUMN email_address TYPE TEXT;
ALTER TABLE project.companies
  ALTER COLUMN phone_number TYPE TEXT;
ALTER TABLE project.companies
  ALTER COLUMN web_address TYPE TEXT;

The example above shows the correct way of multiple alterations. Although it contains a lot of repetition. There is another way how to write less code in PostgreSQL. We can batch the commands together and write much concise code. The whole block of code will look like this:

ALTER TABLE project.companies
ALTER COLUMN name TYPE TEXT,
ALTER COLUMN street TYPE TEXT,
ALTER COLUMN city TYPE TEXT,
ALTER COLUMN post_code TYPE TEXT,
ALTER COLUMN email_address TYPE TEXT,
ALTER COLUMN phone_number TYPE TEXT,
ALTER COLUMN web_address TYPE TEXT;