PostgreSQL RIGHT函数报错：不存在right(numeric, integer)函数

不需要修改列值本身，问题核心是RIGHT函数仅支持字符串类型输入，而你的purchase_order_history.poh1_gl列是numeric（数值）类型，直接调用RIGHT会因参数类型不匹配报错。

只需在调用RIGHT前，将numeric类型的列转换为字符串类型即可，两种常用实现方式：

1. 使用PostgreSQL原生类型转换语法::text：

   RIGHT(purchase_order_history.poh1_gl::text, 2) AS "Dept"
   

2. 使用标准SQL的CAST()函数：

   RIGHT(CAST(purchase_order_history.poh1_gl AS text), 2) AS "Dept"
   

修改后的完整SQL如下：

SELECT DISTINCT
    purchase_order_master.po1_num AS "PO #", 
    RIGHT(purchase_order_history.poh1_gl::text, 2) AS "Dept",
    purchase_order_history.poh1_gl,
    purchase_order_master.po1_podate AS "PO Date", 
    purchase_order_history.poh1_gl AS "GL Account #", 
    purchase_order_master.po1_dept, 
    purchase_order_master.po1_vname AS "Vendor Name", 
    purchase_order_master.po1_vnum AS "Vendor Number"
    
FROM
    purchase_order_master
    INNER JOIN
    purchase_order_line_items
    ON 
        purchase_order_master.po1_num = purchase_order_line_items.po2_num AND
        purchase_order_master.po1_arid = purchase_order_line_items.po2_arid
    INNER JOIN
    purchase_order_history
    ON 
        purchase_order_line_items.po2_num = purchase_order_history.poh1_ponum AND
        purchase_order_line_items.po2_arid = purchase_order_history.poh1_arid
ORDER BY
 "PO Date" DESC;

如果后续频繁需要对该列做字符串操作，也可以考虑修改列类型为text或varchar，但临时类型转换是更灵活的方案，不会改动原有数据结构。

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