New to postgres.
I have a column called "customer_id" in Postgres currently defined as "character varying".
Looking in the DB, a lot of the rows have NULL.
I need this column to be an INTEGER with possible NULL value.
How can I do that in postgres?
I tried running query :
alter table leads alter column customer_id type INTEGER using CAST("customer_id" as INTEGER);
But I get an error saying:
default for column "customer_id" cannot be cast automatically to type integer
As I understood, you need cast "character varying" column to INTEGER.
It seems that you have default value for this "character varying" column and postgres can't "attach" that value for INTEGER type.
If so, you can first drop default value, convert column to INTEGER and add default value appropriate for INTEGER type. like this:
alter table leads alter customer_id drop default;
alter table leads alter column customer_id type INTEGER using CAST("customer_id" as INTEGER);
alter table leads alter customer_id set default <YOUR DEFAULT VALUE HERE>;
