CODEWITHSUNDEEP
postgresqlplpgsql
Pavel Murnikov
Pavel Murnikov

Reputation: 113

Postgres INSERT text column accepts only numeric characters convertible to bigint

I have a table and a function for maintaining it, which is called from a client application. Below is a snippet with the table definition and plpgsql function in question. For the sake of brevity in omitted related ALTER and GRANT commands.

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,
        arg_last_registered, 
        arg_platform, 
        arg_device, 
        arg_operating_system, 
        arg_application_version_number, 
        arg_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, '','','','','');
-- produce
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

Tried changing column and argument data types w/o any success.

Upvotes: 0

Views: 36

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22952

It gives you a line number. If you count from the start of the function $BODY$ that puts you around

RETURNING registration_token INTO var_registration_token;

You have defined var_registration_token as bigint and registration_token appears to be text.

Upvotes: 2

Related Questions