Re: pgsql: JSON_TABLE: propagate table-level ON ERROR to columns per SQL st

On Sat, Sep 19, 2026 at 10:37 AM Alexander Korotkov
<akorotkov(at)postgresql(dot)org> wrote:
> JSON_TABLE: propagate table-level ON ERROR to columns per SQL standard

This commit has introduced a dump/restore problem. Consider the
following test case:

CREATE VIEW v AS SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS
(a int PATH '$' NULL ON ERROR) ERROR ON ERROR) jt;
SELECT * FROM v;

This returns a single-row, single-column result, containing null.

But if you use pg_get_viewdef(), you see that the NULL ON ERROR has vanished:

rhaas=# select * from pg_get_viewdef('v');
pg_get_viewdef
------------------------------------------------------
SELECT a +
FROM JSON_TABLE( +
'"err"'::jsonb, '$' AS json_table_path_0+
COLUMNS ( +
a integer PATH '$' +
) ERROR ON ERROR +
);
(1 row)

And the result of that omission is that if you dump and restore such a
database, the behavior of the view changes as compared to the
original:

$ createdb restore
$ pg_dump | psql restore
$ psql restore
restore=# select * from v;
ERROR: invalid input syntax for type integer: "err"

--
Robert Haas