如何将字段从 bigint 更改为 postgresql 中不同的字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5947025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I alter a field from bigint to character varying in postgresql?
提问by danidacar
I get an error "incompatible types: bigint and character varying." but I know there usually is a trick to bypass this.
我收到错误消息“类型不兼容：bigint 和字符不同。” 但我知道通常有一个技巧可以绕过这个。
回答by Denis de Bernardy
Seems to work fine in PG 9.0, but if not in your version you can always convert to text first:
似乎在 PG 9.0 中工作正常，但如果不是在您的版本中，您始终可以先转换为文本：
select 1::bigint::text::varchar;
回答by Scott Marlowe
alter table abc alter column def type varchar using def::varchar;
回答by JayRizzo
First off, Thank you "Denis de Bernardy" (commented above),
首先，谢谢“Denis de Bernardy” （上面评论），
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...
最近在学习PostgreSQL VS Redshift SQL，查询有什么不同...
So trying to do an update in RedShift it worked like so:
所以尝试在 RedShift 中进行更新，它的工作方式如下：
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.
但是在 Postgres 9.3 中，我的查询失败了。
So after much research and trying to find anything to make it work...
因此，经过大量研究并试图找到任何使其工作的方法......
In the end, all I add to add was the ::varcharto the end of the fieldname like so:
最后，我添加的只是::varchar在字段名的末尾，如下所示：
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.
原因是 postgresql substring() 需要文本，而我的字段名是 BIGINT，这阻止了我无法使用子函数。
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
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
请注意：同样，我所要做的就是将要在子字符串查询中使用的字段转换为包含 ::varchar
Again,
再次，
Thank you.
谢谢你。
