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?

Not like you describe. Just run one statement per transaction. If you can't do that, you need to use subtransactions in PL/PgSQL DO blocks. — Craig Ringer
– Craig Ringer, Commented Jun 11, 2015 at 11:47
Does squirrel support using savepoints around statements? If yes, then enable that. — user330315
– user330315, Commented Jun 11, 2015 at 12:09
Thank's for your answers! One wish: Can someone please rate my question/answer? I need reputation to rate others here...:-) — user4327703
– user4327703, Commented Jun 12, 2015 at 6:30

Community · Accepted Answer · 2017-05-23 12:22:21Z

0

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)');

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jun 11, 2015 at 12:07

user4327703

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: skip erros in SQL clients using JDBC

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related