Postgres中如何正确使用列表达式查询其他表数据？

问题根源 #

你遇到的错误是数据类型不匹配导致的：

• users表的default_travel_method_id、default_shipping_address_id是character varying类型
• 关联的methods、addresses表的id是bigint类型

PostgreSQL对类型一致性要求严格，不会像Oracle那样自动隐式转换字符串和数值类型，必须显式做类型转换或调整关联逻辑。

解决方案 #

方案1：显式类型转换（修正原有子查询） #

在子查询的等值判断中，把字符串类型的ID转换为bigint，可以用PostgreSQL简写转换语法::bigint，或者标准CAST()函数：

SELECT
    created_by,
    (SELECT reference FROM methods WHERE id = default_travel_method_id::bigint) AS default_travel_id,
    (SELECT reference FROM addresses WHERE id = default_shipping_address_id::bigint) AS defaultShippingAddressId
FROM users;

使用CAST()的写法：

SELECT
    created_by,
    (SELECT reference FROM methods WHERE id = CAST(default_travel_method_id AS bigint)) AS default_travel_id,
    (SELECT reference FROM addresses WHERE id = CAST(default_shipping_address_id AS bigint)) AS defaultShippingAddressId
FROM users;

方案2：使用JOIN替代子查询（更高效推荐） #

当需要关联多表查询时，用LEFT JOIN（避免因关联表无匹配数据导致主表数据丢失）比子查询更清晰，性能也更优，尤其适合数据量较大的场景：

SELECT
    u.created_by,
    m.reference AS default_travel_id,
    a.reference AS defaultShippingAddressId
FROM users u
LEFT JOIN methods m ON m.id = u.default_travel_method_id::bigint
LEFT JOIN addresses a ON a.id = u.default_shipping_address_id::bigint;

方案3：从根源修复（修改表字段类型） #

如果业务允许，建议直接修改users表的字段类型，将default_travel_method_id和default_shipping_address_id改为bigint，彻底避免类型转换的麻烦：

ALTER TABLE users
    ALTER COLUMN default_travel_method_id TYPE bigint USING default_travel_method_id::bigint,
    ALTER COLUMN default_shipping_address_id TYPE bigint USING default_shipping_address_id::bigint;

修改后，原有Oracle风格的子查询即可直接在PostgreSQL中执行。

内容的提问来源于stack exchange，提问作者Peter Penzov