Well, I suppose replacing simple copy with procedural per-row function would give huge performance hit. Also what method do you propose to use in the code? Savepoints? I'd say this would also add a more slowdown.
Also quite a bit of boilerplate code would be needed. It's similar to merge statement. Yes, it can be achieved in pure SQL, but having clean merge statement saves you a lot of headache and is usually much more efficient.
Basically, it's not that what OP needs is not doable at all, it's that other RDBMs often has this functionality in much more convenient and performance optimized way.
Best regards, Vitalii Tymchyshyn
On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:
> Well, it OT here and belongs to -hackers, but as for me main use case here
> is ETL or ELT process getting a lot of unvalidated external data.
> And a good option to solve this problem is not to change transaction
> semantics or slow down processing by adding tons of savepoints, but add "on
> error" clause to insert/copy statement.
>
> This clause should allow to save records that can't fit into destination
> table because of type, check of referential constaints into error table.
> Oracle has similar functionality and we are using it in our project. No
> error is generated - no transaction rollback, batch abort or similar.
>
> As for me it would cover 90% of use cases and would be really usefull. The
> one problem I can see is with inserting into partition parent.
PL/PGSQL provides this functionality. It requires the creation of a server
side function and using that function to insert data, but it can do exactly
what you're describing.
While adding other mechanisms to make it "easier" or "more like some other
software" might be valuable; the simple fact is that Postgres _does_ support
what you want. The fact that you're not aware of it doesn't change that.
--
Bill Moran