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

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