Hi Jim, Thank you so much for the kind review.
Architect is pressing for a native procedure to data load. I shall Google ans try to find more suitable one than writing one by myself. Thanks again, Arun On Wed, 17 Jan, 2024, 01:58 Jim Nasby, <jim.na...@gmail.com> wrote: > On 1/16/24 6:34 AM, arun chirappurath wrote: > > I am trying to load data from the temp table to the main table and catch > > the exceptions inside another table. > > I don't have a specific answer, but do have a few comments: > > - There are much easier ways to do this kind of data load. Search for > "postgres data loader" on google. > > - When you're building your dynamic SQL you almost certainly should have > some kind of ORDER BY on the queries pulling data from > information_schema. SQL never mandates data ordering except when you > specifically use ORDER BY, so the fact that your fields are lining up > right now is pure luck. > > - EXCEPTION WHEN others is kinda dangerous, because it traps *all* > errors. It's much safer to find the exact error code. An easy way to do > that in psql is \errverbose [1]. In this particular case that might not > work well since there's a bunch of different errors you could get that > are directly related to a bad row of data. BUT, there's also a bunch of > errors you could get that have nothing whatsoever to do with the data > you're trying to load (like if there's a bug in your code that's > building the INSERT statement). > > - You should look at the other details you can get via GET STACKED > DIAGNOSTICS [2]. As far as I can tell, your script as-written will > always return the first column in the target table. Instead you should > use COLUMN_NAME. Note that not every error will set that though. > > 1: > > https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-ERRVERBOSE > 2: > > https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS > -- > Jim Nasby, Data Architect, Austin TX > >
