I have a small table and a certain field contains the type "character varying". I'm trying to change it to "Integer" but it gives an error that casting is not possible.
Is there a way around this or should I just create another table and bring the records into it using a query.
The field contains only integer values.
Best Answer
There is no implicit (automatic) cast from
text
orvarchar
tointeger
(i.e. you cannot pass avarchar
to a function expectinginteger
or assign avarchar
field to aninteger
one), so you must specify an explicit cast using ALTER TABLE ... ALTER COLUMN ... TYPE ... USING:Note that you may have whitespace in your text fields; in that case, use:
to strip white space before converting.
This shoud've been obvious from an error message if the command was run in
psql
, but it's possible PgAdmin-III isn't showing you the full error. Here's what happens if I test it inpsql
on PostgreSQL 9.2:Thanks @muistooshort for adding the
USING
link.See also this related question; it's about Rails migrations, but the underlying cause is the same and the answer applies.
If the error still occurs, then it may be related not to column values, but indexes over this column or column default values might fail typecast. Indexes need to be dropped before ALTER COLUMN and recreated after. Default values should be changed appropriately.