Postgresql数据库字段连接Null问题及解决方案

一、引出问题

pg数据在做字段连接时，会出现丢字段现象，情况如下

数据准备：

create table table_test(
id numeric,
name varchar,
datetime timestamp(6)
);

insert into table_test values (1,null,null);
insert into table_test values (null,'test',null);
insert into table_test values (3,'',null);
insert into table_test values (4,'',now());
--数据情况
select * from table_test;

利用常用的连接方式，得到结果

select  id||name||datetime from table_test;
select concat(id,name,datetime) from table_test;
select concat_ws('|', id,name,datetime) from table_test;

      通过查询结果可知，有null值的数据会出现缺省，例如第三个查询三个字段以‘|’分隔符，1、2、3三个结果出现缺失分割符，但是4中的数据缺失很完整，结论证明用 '' 去代替 null值可解决该问题，因为可构建函数来解决该问题。

二、解决问题

      将各种类型入参分别构建函数，结果如下：

CREATE OR REPLACE FUNCTION public.f_2null(indata character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
 DECLARE
         outdate varchar;

BEGIN
     select case when indata is null then '' else indata end into outdate;
  
  return outdate;
                
END;
$function$
;
CREATE OR REPLACE FUNCTION public.f_2null(indata date)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
 DECLARE
         outdate varchar;

BEGIN
     select case when indata is null then '' else  indata::varchar end into outdate;
  
  return outdate;
                
END;
$function$
;
CREATE OR REPLACE FUNCTION public.f_2null(indata timestamp without time zone)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
 DECLARE
	 outdate varchar;

BEGIN
     select case when indata is null then '' else indata::varchar end into outdate;
  
  return outdate;
		
END;
$function$
;
CREATE OR REPLACE FUNCTION public.f_2null(indata timestamp with time zone)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
 DECLARE
         outdate varchar;

BEGIN
     select case when indata is null then '' else indata::varchar end into outdate;
  
  return outdate;
                
END;
$function$
;
CREATE OR REPLACE FUNCTION public.f_2null(indata numeric)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
 DECLARE
         outdate varchar;

BEGIN
     select case when indata is null then '' else indata::varchar end into outdate;
  
  return outdate;
                
END;
$function$
;

三、验证结果

select  f_2null(id)||f_2null(name)||f_2null(datetime) from table_test;
select concat(f_2null(id),f_2null(name),f_2null(datetime)) from table_test;
select concat_ws('|', f_2null(id),f_2null(name),f_2null(datetime)) from table_test;

     结果所有字段均已显现，达成目标！