PostgreSQL：将bytea转换为bigint

Question

我需要将一个bytea类型的数据用于查询时转换成bigint。有什么方法可以实现吗？

更多信息：

我有一个如下的Hibernate仓库 -

 @Query(value = "update Sample_Table set other_id = ?1 where id = ?2", nativeQuery = true)
 void saveOrUpdateOtherId(Long other_id, Long id);

由于 Hibernate 在 where 子句中将 id 当作 bytea，而 'Sample_Table' 表的这个 id 字段是 bigint，因此出现了类型不匹配的问题。

我尝试使用 CAST 将 bytea 转换为 bigint，但没有成功，错误消息显示无法将 bytea 转换为 bigint。

如何将 bytea 更改为 bigint？

---

编辑：

Sample_Table DAO：

@Table(name = "Sample_Table")
public class Sample{
    @Id
    @Column(name = "id", unique = true)
    @GeneratedValue
    private Long id;

    @Column(name = "other_id")
    private Long other_id;
}

id字段在这里被定义为长整型。

---

编辑-2 如果有人遇到这样的问题，很可能是在查询中传递了空值。

- Manish Shukla

---

'id'字段在这里被定义为Long（bigint）。不确定为什么8位大整数在这里被解释为字节数组。 - Manish Shukla

当您确认表格确实使用了 bigint 时，我非常怀疑 Spring 在以一种疯狂的方式映射您的 Long。您确定您将一个 Long 传递到 saveOrUpdateOtherId 函数中吗？您尝试过非本地查询吗？它是否以相同的方式失败？ - mabi

系统升级请求及其实际实施至少需要一周时间。目前仍不清楚最新版本修复了哪些Postgres问题。 - Manish Shukla

已经查看了类似的SO帖子。https://dev59.com/bGQo5IYBdhLWcg3wdPEa和https://dev59.com/V2865IYBdhLWcg3wlvqq他们可能通过调整查询来解决了这个问题。但是我的查询非常简单，不知道仓库查询是否需要进行一些修改。 - Manish Shukla

@mabi - 我已经验证了我正在传递一个长整型值。我还没有尝试过非本地查询，我会试一试。 - Manish Shukla

Answer 1

我遇到了一个问题，针对一个插入具有可空列的记录的存储库查询。当该列的值为null时，hibernate使用了错误类型，我会从Postgres收到如下异常：

cannot cast type bytea to bigint

最终找到了这篇博客文章并解决了问题：http://www.carbonrider.com/2020/08/15/org-postgresql-util-psqlexception-error-cannot-cast-type-bytea-to-uuid/， 解决方法是使用 Hibernate 的 TypedParameterValue。 以下是他们的代码片段：

@Query("select * from user where firstname=:name and id=:id", nativeQuery=true)
public List<user> findByNameAndId(@Param("name") String firstName, @Param("id")TypedParameterValue id);

UUID userId = ... //Retrived from request parameter.
TypedParameterValue userIdParam = new TypedParameterValue(new PostgresUUIDType(), userId);
userRepository.findByNameAndId(userName, userIdParam);

与其使用一个只针对Hibernate的解决方案，不如使用一个纯粹JPA的解决方案，但是‍♂️。非常感谢“Carbon Rider”或其他添加该帖子的人！

- mowwwalker

---

参见 > CAST(CAST(?1 AS TEXT) AS BIGINT) > https://dev59.com/-1oV5IYBdhLWcg3wAqvy#62321877 - user1503636

对于其它类型，可以使用StandardBasicTypes类来指定类型。 - Shekhavat

Answer 2

以下表达式对我有效，可将bytes::bytea转换为bigint:

get_byte(bytes, 0)::bigint << 8
    | get_byte(bytes, 1) << 8
    | get_byte(bytes, 2) << 8
    | get_byte(bytes, 3) << 8
    | get_byte(bytes, 4) << 8
    | get_byte(bytes, 5) << 8
    | get_byte(bytes, 6) << 8
    | get_byte(bytes, 7)

这可以正确处理符号位。

- Ben Whitmore

---

你能添加一个使用的例子吗？ - Joseph Katzman

当我传递 E'\\x100000' 时，代码失败了。 - Joseph Katzman

Answer 3

看起来没有一种直接的函数可以将bytea（一个内存块）转换为基本数据类型，除非通过正确填充的十六进制字符串从位数据类型传递：

SELECT ('x'||lpad(encode('\001'::bytea, 'hex'), 16, '0'))::bit(64)::bigint

如果您的 bytea 已经是 8 个字节，并且您的 Postgres 安装运行在默认设置 bytea_output = 'hex' 的情况下，可以通过将其转换为文本并删除前导反斜杠来进行操作：cast to text and remove the leading backslash。

SELECT right(bytea_val::text, -1)::bit(64)::bigint
FROM (SELECT '\x0000000000000001'::bytea as bytea_val) x

- Ezequiel Tolnay

Answer 4

您的其中一个参数是null，无法转换为bigint。

- Jan Nielsen

Answer 5

正如Jan Nielsen所说：

你的其中一个参数为null，无法转换为bigint。

你正在尝试在查询中传递null值。

- Juan Carlos Alafita