Postgres, in an ALTER TABLE ALTER COLUMN statement, sometimes needs USING clause


I'm no Postgres guy.

When you run a <modifyDataTypeChange> on Postgres (8.4 in my case), there are cases where PostgreSQL needs a "USING column_name::int" clause.

In my case I had created a column like this:

<column name="ceeb_code" type="varchar(100)" />

...then needed to change its data type to integer:

<modifyDataType tableName="organization" columnName="ceeb_code" newDataType="integer"/>

...which dies on Postgres. It results in something like:

ALTER TABLE organization ALTER COLUMN ceeb_code SET TYPE int;

...which dies with a type conversion error (even though the table is empty (no rows) and I've never put anything into it. Turns out for mysterious reasons this statement needs to be:

ALTER TABLE organization ALTER COLUMN ceeb_code SET TYPE int USING ceeb_code::integer;

I don't even know what this syntax is.

I worked around it with a modifySql statement, but this probably? maybe? needs to go in core. I hope someone who knows Postgres a lot better than I do can figure this one out.

Assigning to Nathan off the bat so he can route it properly.




Laird Nelson

Fix versions

Affects versions