How to change column datatype from character to numeric in PostgreSQL 8 4

Question

I am using following query   ALTER TABLE presales ALTER COLUMN code TYPE numeric 10 0      to change the datatype of a column from character 20  to numeric 10 0  but I am getting the error      column  code  cannot be cast to type numeric

User · Answer

Step 1  Add new column with integer or numeric as per your requirement Step 2  Populate data from varchar column to numeric column Step 3  drop varchar column Step 4  change new numeric column name as per old varchar column

User · Answer

You can try using USING      The optional USING clause specifies how to compute the new column value from the old  if omitted  the default conversion is the same as an assignment cast from old data type to new  A USING clause must be provided if there is no implicit or assignment cast from old to new type    So this might work  depending on your data    alter table presales alter column code type numeric 10 0  using code  numeric  -- Or if you prefer standard casting    alter table presales alter column code type numeric 10 0  using cast code as numeric     This will fail if you have anything in code that cannot be cast to numeric  if the USING fails  you ll have to clean up the non-numeric data by hand before changing the column type

User · Answer

If your VARCHAR column contains empty strings  which are not the same as NULL for PostgreSQL as you might recall  you will have to use something in the line of the following to set a default   ALTER TABLE presales ALTER COLUMN code TYPE NUMERIC 10 0              USING COALESCE NULLIF code       NUMERIC  0      found with the help of this answer

[postgresql] How to change column datatype from character to numeric in PostgreSQL 8.4

Examples related to postgresql