postgresql 两个null比较

发布网友发布时间：2022-05-01 00:14

共2个回答

懂视网时间：2022-05-01 04:35

the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base (see note below). select * from json_populate_recordset(null::myrowtype, ‘[{"a":1,"b":2},{"a":3,"b":4}]‘)

 a | b
---+---
 1 | 2
 3 | 4

其中参数null::myrowtype,是已存在表的表名，调用如下：

select * from json_populate_recordset(null::"poiarray",'[{"distance":"42","direction":"East","tel":"0731-88876770","name":"中南大学北校区出版社","weight":"0.0","typecode":"141105","longitude":"112.932278","address":"麓山南路932 号中南大学","latitude":"28.169033","type":"科教文化服务;传媒机构;出版社","poiid":"B02DB05LP4"},{"distance":"155","direction":"SouthEast","tel":"","name":"中国银行(中南大学支行)","weight":"0.0","typecode":"160104","longitude":"112.932647","address":"中南大学六食堂后侧","latitude":"28.167892","type":"金融保险服务;银行;中国银行","poiid":"B02DB0UM9V"},{"distance":"102","direction":"West","tel":"","name":"矿冶园","weight":"0.0","typecode":"110100","longitude":"112.930803","address":"麓山南路932号中南大学","latitude":"28.169026","type":"风景名胜;公园广场;公园广场","poiid":"B0FFFZ13U8"},{"distance":"196","direction":"SouthWest","tel":"","name":"中南大学北校区商学院","weight":"0.0","typecode":"141201","longitude":"112.93034","address":"麓山南路932号中南大学","latitude":"28.167933","type":"科教文化服务;学校;高等院校","poiid":"B02DB104AT"},{"distance":"126","direction":"SouthWest","tel":"","name":"中南大学北校区地球科学与信息物理学院","weight":"0.0","typecode":"141201","longitude":"112.930726","address":"麓山南路932号中南大学","latitude":"28.168536","type":"科教文化服务;学校;高等院校","poiid":"B02DB107ER"}]');

对于null::"poiarray"参数，表poiarray必须存在，其结构与json数据相对应，即

create table poiarray(
		distance varchar,
		direction varchar,
		tel varchar,
		name varchar,
		weight varchar,
		typecode varchar,
		longitude varchar,
		address varchar,
		latitude varchar,
		type varchar,
		poiid varchar
);

执行结果如下

技术分享

postgreSQL中函数json_populate_recordset的参数null::myrowtype如何使用

标签：

热心网友时间：2022-05-01 01:43

postgresql中有COALESCE函数，可以替换null，LZ可以使用这个函数来试下。
COALESCE(A.name,'') = COALESCE(B.name,'') 这样就会把null替换为0 那么就会值相同了，出来的结果中 LZ要注意看下是不是正确的。