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

[Robert Haas]

On Sat, Sep 19, 2026 at 10:37 AM Alexander Korotkov
<akorot...@postgresql.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