当前位置：文江博客话题详情

如何在 postgresql 中将字段从 bigint 更改为字符变化？

发布于 2024-11-06 06:59:46 字数 54 浏览 0 评论 0原文

我收到错误“不兼容的类型：bigint 和字符变化”。但我知道通常有一个技巧可以绕过这个问题。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

天气好吗我好吗 2024-11-13 06:59:46

似乎在 PG 9.0 中工作正常，但如果不是在您的版本中，您始终可以先转换为文本：

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

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;

回复收藏 0 原文

白云悠悠 2024-11-13 06:59:46

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

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

回复收藏 0 原文

暖伴 2024-11-13 06:59:46

首先，谢谢“Denis de Bernardy”（上面评论），

我正在尝试更新一个字段，这非常有帮助。

最近，我一直在学习 PostgreSQL VS Redshift SQL 以及查询有何不同...

所以尝试在 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');

但在 Postgres 9.3 中我的查询失败了。

因此，经过大量研究并尝试找到任何使其工作的方法...

最后，我添加的所有内容都是在字段名末尾添加 ::varchar ，如下所示：

fieldname::varchar

原因是postgresql substring() 需要文本，而我的字段名是 BIGINT，这阻止了我无法使用该子函数。

因此，要比较查询：

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');

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');

请注意：我所要做的就是将子字符串查询中使用的字段转换为包含 ::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.

回复收藏 0 原文

~没有更多了~