pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres sql assistance
7+ messages / 5 participants
[nested] [flat]
* postgres sql assistance
@ 2024-01-16 12:34 arun chirappurath <arunsnmimt@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: arun chirappurath @ 2024-01-16 12:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
Attachments:
script.txt (text/plain, 1.9K) download
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres sql assistance
@ 2024-01-16 13:00 Raul Giucich <raul.giucich@gmail.com>
parent: arun chirappurath <arunsnmimt@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Raul Giucich @ 2024-01-16 13:00 UTC (permalink / raw)
To: arun chirappurath <arunsnmimt@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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 (
arunsnmimt@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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres sql assistance
@ 2024-01-16 14:34 Rob Sargent <robjsargent@gmail.com>
parent: Raul Giucich <raul.giucich@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2024-01-16 14:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres sql assistance
@ 2024-01-16 17:54 Ron Johnson <ronljohnsonjr@gmail.com>
parent: arun chirappurath <arunsnmimt@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2024-01-16 17:54 UTC (permalink / raw)
To: pgsql-general
"*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 <arunsnmimt@gmail.com>
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres sql assistance
@ 2024-01-16 20:27 Jim Nasby <jim.nasby@gmail.com>
parent: arun chirappurath <arunsnmimt@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Jim Nasby @ 2024-01-16 20:27 UTC (permalink / raw)
To: arun chirappurath <arunsnmimt@gmail.com>; pgsql-general@lists.postgresql.org
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-DIAGNOSTIC...
--
Jim Nasby, Data Architect, Austin TX
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres sql assistance
@ 2024-01-17 04:04 arun chirappurath <arunsnmimt@gmail.com>
parent: Jim Nasby <jim.nasby@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: arun chirappurath @ 2024-01-17 04:04 UTC (permalink / raw)
To: Jim Nasby <jim.nasby@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.nasby@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-DIAGNOSTIC...
> --
> Jim Nasby, Data Architect, Austin TX
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres sql assistance
@ 2024-01-17 22:41 Jim Nasby <jim.nasby@gmail.com>
parent: arun chirappurath <arunsnmimt@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jim Nasby @ 2024-01-17 22:41 UTC (permalink / raw)
To: arun chirappurath <arunsnmimt@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-01-17 22:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-16 12:34 postgres sql assistance arun chirappurath <arunsnmimt@gmail.com>
2024-01-16 13:00 ` Raul Giucich <raul.giucich@gmail.com>
2024-01-16 14:34 ` Rob Sargent <robjsargent@gmail.com>
2024-01-16 17:54 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-16 20:27 ` Jim Nasby <jim.nasby@gmail.com>
2024-01-17 04:04 ` arun chirappurath <arunsnmimt@gmail.com>
2024-01-17 22:41 ` Jim Nasby <jim.nasby@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox