PostgreSQL中text列仅接受可转换为bigint的数值字符问题排查

我有一个供客户端应用调用的表及维护用PL/pgSQL函数，表定义中registration_token为text类型。调用函数时，传入非数值字符（如'xx'）会触发"invalid input syntax for type bigint: "xx""错误，传入数值字符（如'22'）则正常执行。尝试修改列和参数数据类型后问题仍未解决，现寻求解决方案。

相关表定义与函数代码 #

表定义 #

CREATE TABLE IF NOT EXISTS public.registered_device
(
    device_id serial primary key,
    registration_token text NOT NULL,
    account_id bigint,
    last_registered timestamp without time zone NOT NULL DEFAULT timezone('Europe/Tallinn'::text, CURRENT_TIMESTAMP),
    platform text COLLATE pg_catalog."default",
    device text COLLATE pg_catalog."default",
    operating_system text COLLATE pg_catalog."default",
    application_version_number text COLLATE pg_catalog."default",
    application_build_number text COLLATE pg_catalog."default",
    is_muted boolean NOT NULL DEFAULT false,
    CONSTRAINT registered_device_account_id_fkey FOREIGN KEY (account_id)
        REFERENCES public.users (uid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);
-- owner and grants omitted to save space

函数代码 #

CREATE OR REPLACE FUNCTION public.register_device(
    arg_registration_token text,
    arg_account_id bigint,
    arg_last_registered timestamp without time zone,
    arg_platform text,
    arg_device text,
    arg_operating_system text,
    arg_application_version_number text,
    arg_application_build_number text)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE var_registration_token BIGINT;
BEGIN
UPDATE public.pilates_registered_device
SET 
    account_id = arg_account_id,  
    last_registered = arg_last_registered, 
    platform = arg_platform, 
    device  = arg_device, 
    operating_system  = arg_operating_system, 
    application_version_number  = arg_application_version_number, 
    application_build_number  = arg_application_build_number
WHERE registration_token = arg_registration_token
RETURNING registration_token INTO var_registration_token;

IF var_registration_token IS NULL THEN 
    INSERT INTO public.registered_device(
        registration_token, 
        account_id,
        last_registered, 
        platform, 
        device, 
        operating_system, 
        application_version_number, 
        application_build_number
    )
    
        VALUES (
        arg_registration_token,
        arg_account_id,
        last_registered, 
        arg_platform, 
        arg_device, 
        operating_system, 
        application_version_number, 
        application_build_number
        )
    RETURNING registration_token INTO var_registration_token;
END IF;
return var_registration_token;
END;                    
$BODY$;

测试示例与错误信息 #

触发错误的调用 #

select public.register_device('xx', 1,  localtimestamp, '','','','','');

错误信息：

ERROR:  invalid input syntax for type bigint: "xx"
CONTEXT:  PL/pgSQL function register_device(text,bigint,timestamp without time zone,text,text,text,text,text) line 17 at SQL statement 
SQL state: 22P02

正常执行的调用 #

select public.register_device('22', 1,  localtimestamp, '','','','','');
-- runs w/o errors

问题原因与解决方案 #

问题原因 #

函数中声明的变量var_registration_token类型为BIGINT，但表中registration_token是TEXT类型。当执行UPDATE或INSERT的RETURNING registration_token INTO var_registration_token时，PostgreSQL会尝试将TEXT类型的返回值隐式转换为BIGINT：

• 若registration_token是纯数字文本（如'22'），转换成功，函数正常执行；
• 若registration_token是非数值文本（如'xx'），转换失败，抛出类型转换错误。

此外，函数中UPDATE操作的表是public.pilates_registered_device，但INSERT操作的表是public.registered_device，这大概率是笔误；同时INSERT的VALUES部分存在参数漏写arg_前缀的问题，会导致使用表默认值而非传入参数。

解决方案 #

1. 修正变量类型：将DECLARE部分的var_registration_token类型改为TEXT，与表中列类型保持一致；
2. 统一表名：将UPDATE操作的表名改为public.registered_device，与INSERT操作的表保持一致；
3. 补全参数前缀：修正INSERT的VALUES部分参数，补全arg_前缀，确保使用传入的参数值。

修改后的完整函数代码：

CREATE OR REPLACE FUNCTION public.register_device(
    arg_registration_token text,
    arg_account_id bigint,
    arg_last_registered timestamp without time zone,
    arg_platform text,
    arg_device text,
    arg_operating_system text,
    arg_application_version_number text,
    arg_application_build_number text)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE var_registration_token TEXT; -- 修正变量类型为TEXT
BEGIN
UPDATE public.registered_device -- 统一表名
SET 
    account_id = arg_account_id,  
    last_registered = arg_last_registered, 
    platform = arg_platform, 
    device  = arg_device, 
    operating_system  = arg_operating_system, 
    application_version_number  = arg_application_version_number, 
    application_build_number  = arg_application_build_number
WHERE registration_token = arg_registration_token
RETURNING registration_token INTO var_registration_token;

IF var_registration_token IS NULL THEN 
    INSERT INTO public.registered_device(
        registration_token, 
        account_id,
        last_registered, 
        platform, 
        device, 
        operating_system, 
        application_version_number, 
        application_build_number
    )
    
        VALUES (
        arg_registration_token,
        arg_account_id,
        arg_last_registered, -- 补全arg_前缀
        arg_platform, 
        arg_device, 
        arg_operating_system, -- 补全arg_前缀
        arg_application_version_number, -- 补全arg_前缀
        arg_application_build_number -- 补全arg_前缀
        )
    RETURNING registration_token INTO var_registration_token;
END IF;
return var_registration_token;
END;                    
$BODY$;

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