How to "insert into select from" skipping exceptions

Question

1st post on SE/DBA; i am actually more of a DB developer but did not find a better suited forum, let me know if I picked the wrong one. I am new to PG but know Oracle.

I will need to report on JSON data, which is currently held in TEXT columns on Postgres 9.5. I plan to copy this data to proper JSONB fields so i can use the JSON parsing and SQL extensions of PG. Thus something akin to:

insert into T( id, jblob )
select id, jblob::jsonb from S;

Now this will fail if some of the existing rows are not valid JSON. In Oracle I would either

• insert into T select from S LOG ERRORS REJECT LIMIT UNLIMITED

which loads all it can and provides a log of failed rows to be reviewed (my 1st choice)

• iterate over a cursor and log errors myself in an exception handler

which is more work and usually slower

What is the best/most idiomatic way of achieving this with PostgreSQL (even in general, not necessarily for JSON content) ?

I tried "on conflict do nothing" but either used it wrongly or it is only meant to avoid duplicates and still raises an error in my case (with failed JSON casts).

Answer 1

Unfortunately there is no is_json() or similar type check, but you can write your own:

create or replace function is_valid_json(p_text text) 
   returns boolean
as
$$
begin
  if p_text is null then
    return true;
  end if;

  if p_text::json is not null then 
    return true;
  end if;
exception 
  when others then
    return false;
end;
$$
language plpgsql;

Then you could do:

insert into T( id, jblob )
select id, jblob::jsonb 
from S
where is_valid_json(jblob);

Or do the test and cast in one go:

create or replace function cast_if_possible(p_text text) 
   returns jsonb
as
$$
begin
  return p_text::json;
exception 
  when others then
    return null;
end;
$$
language plpgsql;

and

insert into T( id, jblob )
select id, cast_if_possible(jblob) as jblob
from S;

That would store NULL values for invalid JSON values. If you don't want that you would need to add condition to exclude those.

If you have a substantial number of (invalid) duplicate values in the table, then declaring the function as stable then Postgres can cache the results of the function call, which might improve performance.

---

However this is not going to be fast as the exception handler and PL/pgSQL have a substantial performance overhead.