error on altering table column type in postgresql - bigint to character varying

Question

Below is the query:

alter table customer_master alter column pk_customer_id type character varying(20);

I got the following error message:

ERROR: operator does not exist: character varying > integer HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

CREATE TABLE customer_master(
  pk_customer_id bigint NOT NULL,
  created_customer_name character varying(200) DEFAULT NULL::character varying,
  fk_deployment_id bigint NOT NULL,
  is_active integer
 )

here pk_customer_id is the primary key column for this table.

Vivek S. Vivek S. · Accepted Answer · 2015-11-23T11:19:12

This should work

USING as per documentation:

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

ALTER TABLE customer_master
ALTER COLUMN pk_customer_id type CHARACTER VARYING(20) 
USING pk_customer_id::VARCHAR; -- add using keyword

error on altering table column type in postgresql - bigint to character varying

1 Answers