Unnest (xpath()) 不能正确识别 NULL 值 [英] Unnest (xpath()) doesn't identify NULL values correctly

问题描述

PostgreSQL 9.6 版，使用 xml2 扩展.我试图从 XML 中提取行并将它们插入到 postgreSQL 表中.下面是一个简短的例子:

PostgreSQL version 9.6, using the xml2 extension. I'm attempting to extract rows from XML and insert them into a postgreSQL table. Here's an abbreviated example:

ROLLBACK;
BEGIN;
DO $$
DECLARE 
    v_xml xml;
    v_record RECORD;
BEGIN
v_xml := '<?xml version="1.0" encoding="UTF-16"?>
<root>
    <table>
        <row><a>1</a><b>2</b><c>3</c></row>
        <row><a></a><b>5</b><c>6</c></row>
        <row><a>7</a><b>8</b><c>9</c></row> 
    </table>
</root>'::text;

CREATE TEMPORARY TABLE temptable( col_a text, col_b text, col_c text ) ON COMMIT DROP;
INSERT INTO temptable VALUES
(
    unnest(xpath('/root/table/row/a/text()', v_xml))::text,
    unnest(xpath('/root/table/row/b/text()', v_xml))::text,
    unnest(xpath('/root/table/row/c/text()', v_xml))::text
);

-- display table contents
FOR v_record IN SELECT * FROM temptable LOOP
    RAISE NOTICE 'col_a: % col_b: % col_c: %', v_record.col_a, v_record.col_b, v_record.col_c;
END LOOP;
END $$;

当没有值为 NULL 时，这可以正常工作:

This works fine when no values are NULL:

NOTICE:  col_a: 1 col_b: 2 col_c: 3
NOTICE:  col_a: 4 col_b: 5 col_c: 6
NOTICE:  col_a: 7 col_b: 8 col_c: 9

但是，对于缺失值或 NULL 值，unnest() 无法正确识别它们并使用其列数组的下一个值(应该为 next 行读取该值.

However, for missing or NULL values, unnest() doesn't recognize them properly and uses the next value its column array (that should be read for the next row.

为了演示，修改 XML 如下(即，将前面的 4 和 9 值设为空，或者完全删除元素):

To demonstrate, modify the XML as follows (i.e. make the former 4 and 9 values null, or remove the elements completely):

<table>
    <row><a>1</a><b>2</b><c>3</c></row>
    <row><a></a><b>5</b><c>6</c></row>
    <row><a>7</a><b>8</b><c></c></row>  
</table>

现在产生以下(错误的)输出:

Now the following (erroneous) output is produced:

NOTICE:  col_a: 1 col_b: 2 col_c: 3
NOTICE:  col_a: 7 col_b: 5 col_c: 6
NOTICE:  col_a: 1 col_b: 8 col_c: 3
NOTICE:  col_a: 7 col_b: 2 col_c: 6
NOTICE:  col_a: 1 col_b: 5 col_c: 3
NOTICE:  col_a: 7 col_b: 8 col_c: 6

一些调试语句显示三个未嵌套的列数组为 { 1, 7 }, { 2, 5, 8 } 和 { 3, 6 }.插入给定行时，没有占位符 NULL 可用作列值.

Some debugging statements revealed the three unnested column arrays to be { 1, 7 }, { 2, 5, 8 } and { 3, 6 }. There are no placeholder NULLs to be used as column values while inserting a given row.

是否有另一种实现解嵌套的方法可以正确解释空或缺失的节点值?

Is there another way to implement the unnesting that will properly account for null or missing node values?

推荐答案

这不是 UNNEST 的问题，而是(或者更确切地说，是由于)xpath 的问题>，这似乎在返回的数组中不包含 NULL 值.

This is not an issue with UNNEST, it's an issue with (or rather, due to) xpath, which appears to not include NULL values in the returned array.

SELECT XPATH('/root/table/row/a/text()', '

1</a><a>3</a></row></table></root>'::XML) 返回 {1,3}

解决此问题的一种方法是先对元素执行 xpath，然后再访问值:

One way to get around this would be to do an xpath on the elements first, and access the values afterwards:

SELECT (XPATH('/a/text()', u))[1]
FROM UNNEST(XPATH('/root/table/row/a', '<root><table><row><a>1</a><a></a><a>3</a></row></table></root>'::XML)) u

这将返回 3 行(第二行是 NULL):

This returns 3 rows (second one is NULL):

1

3

这篇关于Unnest (xpath()) 不能正确识别 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

登录 关闭

扫码关注1秒登录

发送“验证码”获取 | 15天全站免登陆

友情链接： IT屋 Chrome插件 谷歌浏览器插件

IT屋 ©2016-2022 琼ICP备2021000895号-1 站点地图 站点标签 SiteMap <免责申明> 本站内容来源互联网,如果侵犯您的权益请联系我们删除.