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

On Wed, Sep 23, 2026 at 9:21 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Sat, Sep 19, 2026 at 10:37 AM Alexander Korotkov
> <akorotkov@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"

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

------
Regards,
Alexander Korotkov
Supabase