2

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).

1 Answer 1

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.

8
  • Thank you for the suggestion, but wouldn't this actually perform the json check twice for every (valid) row, once to evaluate the WHERE clause, and a second time to INSERT the valid ones? Would you expect this approach to yield better performance than a FOR loop with exception handling for invalid rows ? Perhaps the function overhead is lower than the row-per-row looping ?
    – PGcritter
    Oct 12, 2018 at 11:10
  • @PGcritter: correct, the check is done twice- however I don't think that is going to be a performance bottleneck. But if you want to optimize it further you can also write a function that does the cast and returns null if it fails. See my edit. In any case this will be a lot faster than doing row-by-row processing
    – user1822
    Oct 12, 2018 at 11:15
  • thank you, a_horse_with_no_name. i am too fresh here for my upvote to be counted but i did read your answer in its entirety and find it useful
    – PGcritter
    Oct 12, 2018 at 11:19
  • @PGcritter: I did a quick and dirty performance test: Casting a valid text of about 1700 characters to JSONB one million times took about 0.2 seconds on my laptop. I don't think you should be concerned about the casting performance
    – user1822
    Oct 12, 2018 at 11:34
  • Thanks again, @a_horse_with_no_name. Can I take it that there is less overhead in wrapping every single cast in a PL/psSQL function rather than the "rather ask forgiveness than permission" approach of handling the occasional exception in a loop ?
    – PGcritter
    Oct 12, 2018 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.