Menu
First, specify the name of the table to which the column you want to change belongs in the ALTER TABLE clause. Second, give the name of column whose data type will be changed in the ALTER COLUMN clause. Third, provide the new data type for the column after the TYPE keyword.
1) Drop the view that is causing the issue, after that run your alter table statement. Then once you altered the table you can create the view again. 2) Or if you cant drop your view, then please follow this particular stackover flow thread=> stackoverflow.com/questions/3243863/…
You can try using USING:
The optional
USINGclause 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. AUSINGclause 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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With