Обсуждение: postgres sql assistance

Dear all,

I am an accidental postgres DBA and learning things every day. Apologies for my questions if not properly drafted.

I am trying to load data from the temp table to the main table and catch the exceptions inside another table.

temp table is cast with the main table data type and trying to load the data.

temp table is below.

    category_name                          |            description                      | is_active
-------------------------------------------+---------------------------------------------+-----------
 Tech123212312312323233213123123123123    | Furniture and home decor                    | true
 Tech123212312312323233213123123123123    | Electronic devices and accessories          | true
 Elec                                    | Books of various genres                     | 15
 TV                                      | Books                                       | 12
 cla                                     | Apparel and fashion accessories             | true

category name is varchar(25) and is_active is boolean in main table. So i should get exceptions for 1st,2nd for category_name rows and 4 and 5th rows for boolean. In exception table results,its only showing 

Exception table is below. Here instead of showing exception for value 12 in the is_active table its showing old exception for 15 itself.. Script is attached,,...SQLERRM value is not getting updated for row 12..WHat could be the reason for this?

value too long for type character varying(25) category_name 1 2024-01-16 16:17:01.279 +0530 value too long for type character varying(25) description 2 2024-01-16 16:17:01.279 +0530 invalid input syntax for type boolean: "15" is_active 3 2024-01-16 16:17:01.279 +0530 invalid input syntax for type boolean: "15" 4 2024-01-16 16:17:01.279 +0530 invalid input syntax for type boolean: "15" 5 2024-01-16 16:17:01.279 +0530

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