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

On Wed, Sep 23, 2026 at 9:21 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> 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"

Thank you for your report. Yep, this requires more detailed
consideration. Reverted for now.

------
Regards,
Alexander Korotkov
Supabase