ERROR: invalid input syntax for type numeric: ""

error_22P02.sql

create table test
(
    name character varying(20),
    id character varying(20) not null
);

insert into test (name, id) values ('bar', '');
insert into test (name, id) values ('foo', '11');
insert into test (name, id) values ('foo', '22');

-- This query is OK
select * from test where id in (11, 22) and name = 'foo';

-- This query is wrong
select * from test where id in (11, 22);

-- and how to solve this problem
create or replace function public.to_number(t varchar)
  returns numeric
as
$BODY$
  select case when t = '' then null else t end::text::numeric;
$BODY$
language sql immutable strict;

create cast (character varying as numeric) with function to_number(varchar) as implicit;