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
This question is related to
postgresql
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
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)
Source: Stackoverflow.com