posgresql （表）字段拼接和判空处理

posgresql （表）字段拼接和判空处理

事例代码:
 SELECT bigdata_zdren_guiji.zdren_id AS id,
    bigdata_zdren_guiji.capture_time AS start_time,
    concat_ws((COALESCE(bigdata_zdren.name, '未知'::character varying))::text, ' 姓名: ', concat_ws((COALESCE((bigdata_zdren.birth)::character varying, '未知'::character varying))::text, ' 出生日期: ', concat_ws((COALESCE(bigdata_country.country, '未知'::character varying))::text, ' 国家: ', concat_ws((COALESCE(bigdata_zdren.native_place, '未知'::character varying))::text, ' 籍贯: ', concat_ws((COALESCE(bigdata_nation.name, '未知'::character varying))::text, ' 民族: ', concat_ws((COALESCE(bigdata_zdren.identity_num, '未知'::character varying))::text, ' 身份证号: ', concat_ws((COALESCE(bigdata_sex.name, '未知'::character varying))::text, ' 性别: ', concat_ws(concat_ws(' '::text, COALESCE(bigdata_zdren.phone_num1, '未知'::character varying), COALESCE(bigdata_zdren.phone_num2, '未知'::character varying)), ' 手机号: ', concat_ws((COALESCE(bigdata_zdren_type.name, '未知'::character varying))::text, ' 人员分类: ', concat_ws((COALESCE(bigdata_zdren.car_num, '未知'::character varying))::text, ' 车牌: ', concat_ws(' wifi_mac: '::text, '', COALESCE(bigdata_zdren.wifi_mac, '未知'::character varying)))))))))))) AS description,
    shebei.name AS loction,
    bigdata_zdren_guiji.type AS name
   FROM bigdata_zdren,
    bigdata_zdren_type,
    bigdata_sex,
    bigdata_nation,
    bigdata_province,
    bigdata_country,
    guiji.bigdata_zdren_guiji,
    shebei
  WHERE ((((((bigdata_zdren.subtype = bigdata_zdren_type.zdren_type) AND (bigdata_zdren.sex = bigdata_sex.type)) AND (bigdata_zdren.race = bigdata_nation.mz_id)) AND (bigdata_zdren.nationality = bigdata_country.prefix)) AND (bigdata_zdren_guiji.zdren_id = bigdata_zdren.id)) AND (bigdata_zdren_guiji.location_id = shebei.location_id))
UNION ALL
 SELECT select_bukongyujing_month.id,
    select_bukongyujing_month.start_time,
    select_bukongyujing_month.description,
    select_bukongyujing_month.loction,
    select_bukongyujing_month.name
   FROM select_bukongyujing_month
  ORDER BY 5 DESC

注意:COALESCE用于判空，CONCAT_WS用于拼接
得到结果,如图: