coalesce函数的用法

coalesce函数的参数是列，结果是取出第一个不为空的列的数据。

postgre数据库中某个视图建立时使用: '002'::text || COALESCE(parent_id, ''::character varying)::text AS parent_id

把某棵树的parent_id置为002或者002+数据表.parent_id(字段）

首先，建一个视图：

CREATE OR REPLACE VIEW v AS SELECT NULL AS c1,NULL AS c2,1 AS c3,NULL AS c4,2 AS c5,NULL AS c6 FROM dual UNION ALL SELECT NULL AS c1,NULL AS c2,NULL AS c3,3 AS c4,NULL AS c5,2 AS c6 FROM dual;

查看视图结果：

使用coalesce函数查结果：

SELECT COALESCE (c1,c2,c3,c4,c5,c6) AS c FROM v;

结果竟是：

难道coalesce函数不支持number类型？带着这个疑问，给转换一下，结果：

SELECT COALESCE (c1,c2,to_char(c3),to_char(c4),to_char(c5),to_char(c6)) AS c FROM v;

好像是真的，那么对date类型的支持呢？试一试

CREATE OR REPLACE VIEW v AS
SELECT to_date('20150101','YYYYMMDD') AS c1,NULL AS c2,1 AS c3,NULL AS c4,2 AS c5,NULL AS c6 FROM dual
UNION ALL
SELECT NULL AS c1,NULL AS c2,NULL AS c3,3 AS c4,NULL AS c5,2 AS c6 FROM dual;

 

结果竟然：

到这里就不禁要想，应该不是不支持某种数据类型，而是把第一列的数据类型，作为整个函数的数据类型了。

这样试试就行了

最后，既然coalesce函数里面是列，那么用*代表所有列行不行呢？

显然是不行的。

总结：

1、coalesce函数是用来获取第一个不为空的列的值

2、coalesce函数里面的数据类型，必须全部都跟第一列的数据类型一致

3、CREATE OR REPLACE VIEW v AS SELECT NULL AS c FROM dual;这样建立的视图，列c的数据类型是char