How can I alter a field from bigint to character varying in postgresql?

I get an error "incompatible types:开发者_开发问答 bigint and character varying." but I know there usually is a trick to bypass this.

Seems to work fine in PG 9.0, but if not in your version you can always convert to text first:

select 1::bigint::text::varchar;

alter table abc alter column def type varchar using def::varchar;

First off, Thank you "Denis de Bernardy" (commented above),

I was trying to update a field and this was very helpful.

Recently, I have been learning PostgreSQL VS Redshift SQL and how the queries are different...

So trying to do an update in RedShift it worked like so:

UPDATE table_name SET fieldname = fieldname + 100 
FROM table_name WHERE substring(fieldname from 4 for 2) 
IN ('01','02','03','04','05','06','07','08','09','10','11','12');

But in Postgres 9.3 my query failed.

So after much research and trying to find anything to make it work...

In the end, all I add to add was the ::varchar to the end of the fieldname like so:

fieldname::varchar

Reason was is that the postgresql substring() expects text and my fieldname was a BIGINT which prevented I couldn't use the subfunction.

So to compare the queries:

OLD Query

UPDATE table_name SET fieldname = fieldname + 100 
FROM table_name WHERE substring(fieldname from 4 for 2) 
IN ('01','02','03','04','05','06','07','08','09','10','11','12');

New Query for Postgres 9.3

UPDATE table_name SET fieldname = fieldname + 100 
FROM table_name WHERE substring(fieldname::varchar from 4 for 2) 
IN ('01','02','03','04','05','06','07','08','09','10','11','12');

Please note: Again all i had to do was convert the field to be used in the substring query to contain the ::varchar

Again,

Thank you.