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.