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