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. · Accepted Answer · 2015-11-23 11:19:12Z

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

this worked for me. thanks. and the issue was not only the query. There was a constraint which checks pk_customer_id field is > 0. so on removing constraint it worked for me.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related