PostgreSQL: skip erros in SQL clients using JDBC

Question

I want to execute a SQL script in a client using JDBC (not the postgreSQL pslq client). In this script I would like to do something like that:

skip errors on;

alter table foo ...;

skip errors off;

Is there a way to do this with PostgreSQL >= 9.1?

Answer 1

I found this thread with a good solution usind DO blocks and error codes: How to continue sql script on error?

And creating a function for my problem:

create or replace function continueOnError(v_sqlStatement text)
  returns void
  language plpgsql
  as ' 
  BEGIN
    execute v_sqlStatement;
  EXCEPTION
    WHEN invalid_schema_name
      OR undefined_table 
      OR undefined_column 
    THEN RAISE WARNING ''Continued on error sql statement: %'', v_sqlStatement;
  END;
';

...use it:

select continueOnError('alter table test add constraint fk_test_valueid foreign key (valueid) references value(id)');