by looking at exceptions i may rollback or commit, i.e. conn.rollback() or conn.commit()
if I rollback table should be empty,
if I commit table should have 2 rows
is there any way is possible ?
Not with PgJDBC at the moment.
Dave pointed you to the patch that you'd need if you want this behaviour. It might get integrated into PgJDBC. You could help by improving the patch to add a configuration option to turn the functionality on/off (default off) and by testing it.
That's really the only way you're going to get a robust version of what you want. The ways others have outlined aren't going to work. ON CONFLICT only handles unique violations and won't help with data that's not valid input for a datatype, nulls where no nulls are permitted, etc.
The closest way supported well by PgJDBC and PostgreSQL way is to set synchronous_commit = off . You can then do a series of autocommit statements, followed at the end by setting synchronous_commit = on and committing the final statement. This will have the performance benefits of avoiding so many WAL flushes while ignoring errors and preserving successful statements only.
set synchronous_commit = off; insert into employee values(1, 'K1');
insert into employee values(1, 'K1');
insert into employee values(2, 'K2');
set synchronous_commit = on;
/* now do something that writes to the database that will NOT fail to make sure everything commits */
Of course this isn't portable to other DBMSes. This isn't exactly the same as what you want because you cannot rollback(). But it's close.
I strongly advise you to look into pull #477 above. Build a patched version of the driver and test it to see if it meets your needs. Follow up with feedback and test results here. Review the code. If you help solve your problem you've got way more chance of seeing a good result.
