Fix Error “column … cannot be cast automatically to type …” in PostgreSQL

If you’re getting an error that reads something like ‘column “c1” cannot be cast automatically to type text[]‘ in PostgreSQL, it could be that you’re trying to change a column’s type to one where the existing type can’t be implicitly cast to.

To fix this issue, try explicitly casting the column to the desired data type.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE t1
(
    c1 text
);

ALTER TABLE t1 ALTER COLUMN c1 TYPE text[];

Result:

ERROR:  column "c1" cannot be cast automatically to type text[]
HINT: You might need to specify "USING c1::text[]".

In this example I created a table with a text column. I then tried to alter that column to a text[] column. The error states that it can’t be done, and then provides a hint as to how to fix the issue.

The above error states that the column can’t be cast automatically to text[], but we could get the same error with other data types. For example we’d get the same error if we tried to change it to an integer:

ALTER TABLE t1 ALTER COLUMN c1 TYPE integer;

Result:

ERROR:  column "c1" cannot be cast automatically to type integer
HINT: You might need to specify "USING c1::integer".

Solution

We can try adding the USING clause to our ALTER TABLE statement:

ALTER TABLE t1 ALTER COLUMN c1 TYPE text[] USING (c1::text[]);

Result:

ALTER TABLE

In this case I explicitly cast the c1 column to the text[] type.

The output indicates that the column was successfully converted.

Bear in mind that any data in the column will need to conform to the new data type. If not, you might get another error.