CODEWITHSUNDEEP
postgresqlalter
Bart Friederichs
Bart Friederichs

Reputation: 33521

Changing a column type from char to array

I have a table in PostgreSQL that has a character(1) column, that I want to change to an text[] column, but I can't seem to get it working:

ALTER TABLE public.mytable
     ALTER COLUMN abc TYPE TEXT[] COLLATE pg_catalog."default"
     USING ARRAY[abc];

gives me this error:

ERROR: default for column "abc" cannot be cast automatically to type text[]

which is understandanle, because Postgres can't cast NULL:bpchar to an array. But, how can I get this done then? Apparently, NULLs can be typed ...

Upvotes: 1

Views: 2052

Answers (1)

Marth
Marth

Reputation: 24812

You'll need to remove the default value, change the data type and re-add the default value.
From the documentation:

(…) the USING expression is not applied to the column's default value (if any); (…).

This means that when there is no implicit or assignment cast from old to new type, SET DATA TYPE might fail to convert the default even though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET DEFAULT to add a suitable new default.

and the given example:

> When the column has a default expression that won't automatically cast to the 
  new data type:

ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();

Upvotes: 1

Related Questions