I get an error "incompatible types: bigint and character varying." but I know there usually is a trick to bypass this.
Add a comment
|
3 Answers
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;
answered May 10, 2011 at 8:26
Denis de Bernardy
79.1k14 gold badges138 silver badges158 bronze badges
Comments
alter table abc alter column def type varchar using def::varchar;
1 Comment
Wouter Lievens
He's talking about casting, not changing the column.
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.
