Re: postgres sql assistance
On 1/16/24 10:04 PM, arun chirappurath wrote: Architect is pressing for a native procedure to data load. It's possible to write a loader in pl/pgsql but it would be easily twice as complex as where you got on your first attempt. It would also never perform anywhere near as well as a dedicated loader, because there's no way to avoid the temp table (which a native loader doesn't need to use). -- Jim Nasby, Data Architect, Austin TX
Re: postgres sql assistance
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, 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 > >
Re: postgres sql assistance
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
Re: postgres sql assistance
"*invalid input syntax for type boolean: "15"*" That is the problem. You can't insert 15 into a column of type "boolean". On Tue, Jan 16, 2024 at 7:35 AM arun chirappurath wrote: > 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 > >
Re: postgres sql assistance
On 1/16/24 06:00, Raul Giucich wrote: Hi Arun, can you share the sql used for this insert. Visually it seems some character are affecting the data. Best regards, Raul Raul, the OP attached the sq.
Re: postgres sql assistance
Hi Arun, can you share the sql used for this insert. Visually it seems some character are affecting the data. Best regards, Raul El mar, 16 ene 2024 a la(s) 9:35 a.m., arun chirappurath ( arunsnm...@gmail.com) escribió: > 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 > >
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
CREATE OR REPLACE FUNCTION insert_temp_data_to_main_table()
RETURNS VOID AS $$
DECLARE
v_main_table_name TEXT := 'main_categories';
v_temp_table_name TEXT := 'tmp_categories';
v_error_table_name TEXT := 'error_log_table';
v_sql_statement TEXT;
BEGIN
-- Clear the error log table
EXECUTE 'TRUNCATE TABLE ' || v_error_table_name;
-- Build the complete SQL statement with aggregated columns and select
clauses
v_sql_statement := format('
INSERT INTO %I (%s)
SELECT %s
FROM %I',
v_main_table_name,
(SELECT string_agg(column_name, ', ') FROM information_schema.columns
WHERE table_name = v_main_table_name),
(SELECT string_agg('CAST(' || v_temp_table_name || '.' || column_name
|| ' AS ' || data_type || ')', ', ') FROM information_schema.columns WHERE
table_name = v_temp_table_name),
v_temp_table_name);
-- Print the SQL statement
RAISE NOTICE 'Generated SQL statement: %', v_sql_statement;
-- Insert data into the main table from the temp table
EXECUTE v_sql_statement;
EXCEPTION
WHEN others THEN
DECLARE
v_error_msg TEXT;
v_failed_column_name TEXT;
v_row_counter INT := 1;
BEGIN
-- Get the specific error message
v_error_msg := SQLERRM;
-- Get the failed column name
SELECT column_name INTO v_failed_column_name
FROM information_schema.columns
WHERE table_name = v_temp_table_name
ORDER BY ordinal_position
LIMIT 1 OFFSET v_row_counter - 1;
-- Log the error into the error log table
EXECUTE format('
INSERT INTO %I (error_message, failed_column_name,
failed_row_number)
VALUES ($1, $2, $3)', v_error_table_name)
USING v_error_msg, v_failed_column_name, v_row_counter;
END;
END;
$$ LANGUAGE plpgsql;
