142

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

Willi Mentzel
  • 27,862
  • 20
  • 113
  • 121
user728630
  • 2,025
  • 5
  • 22
  • 35

3 Answers3

254

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • this column is used as a foreign key in another table, i guess i will have to change the datatype of that too? – user728630 Oct 07 '11 at 05:39
  • 2
    @user728630: You'll have to drop the FK, change both columns, and then add the FK back. You do have a test database to play with and a backup of the production database, right? – mu is too short Oct 07 '11 at 05:42
  • i deleted the foreign key constraint , changed the datatype but after that unable to add the FK. Getting the following error ERROR: insert or update on table "invoices" violates foreign key constraint "invoice_presale_fk" DETAIL: Key (sale,cpf_cnpj)=(4,05943560000101) is not present in table "presales". – user728630 Oct 07 '11 at 06:30
  • @user728630: Is the leading zero on `cpf_cnpj` a problem? Sorry, I don't have enough context to know what's wrong. – mu is too short Oct 07 '11 at 07:26
  • What does the `code::numeric` represent? I can't find any more documentation on the USING syntax or what you could use for that parameter. – funwhilelost Mar 14 '16 at 20:42
  • 2
    @funwhilelost That's a [type cast](http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS). The linked ALTER TABLE docs cover what you can use with USING. – mu is too short Mar 14 '16 at 21:03
  • 3
    @muistooshort I see from the docs it's actually an expression. That makes more sense. The type cast caught me off guard. I ended up with `TYPE varchar(255) USING (substring(formertextcolumn from 1 for 255))` – funwhilelost Mar 14 '16 at 23:22
8

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)

Patru
  • 4,481
  • 2
  • 32
  • 42
-1

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

PIYUSHKUMAR PATEL
  • 1