Question 218722 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgress: custom upsert (stored proc?) when inserting multiple rows at once?

There’s a table:

create table mytab(
  r char(38) not null,
  h char(2),
  p bytea not null,
  primary key (r,h)
)

I’m inserting hundreds of rows at once, like this:

insert into mytab (r, h, p) values 
('val_r', 'a0', 'pr_1'), ('r1', 'a1', 'p1')......

I need to replace the regular insert with a "smart" one:

  1. if there’re no such keys (r,h) then insert the row
  2. if the keys exist and the value (p) is the same as new then do nothing
  3. if the keys exist but the value is different – throw an error for this particular row only, somehow let the app know that this particular row has error – and continue with other rows

I’ve read about insert ... on conflict do update/nothing but it looks like it’s not applicable esp. because of rule 3, in docs I found nothing like "do fail".

So, how can I do it, inserting many rows at once? Maybe something like stored procedure, but how?

PgSQL 15, psycopg2. Using insert one row at a time is not an option due to performance penalty.

Tags:

2

Answers


  1. 0

    Maybe create a procedure with a out parameter and use cursor to select rows from the table and loop through each row. Check within the table mytab if there exists any record with r, h like this.

    DECLARE
existing_count bigint DEFAULT 0;
r_value char(38);
h_value char(2);
p_value bytea;
v_query text;
c1 CURSOR for SELECT r,h,p FROM temp_table;
BEGIN
OPEN c1;
FETCH c1 INTO r_value, h_value, p_value;
EXIT WHEN NOT FOUND;  
SELECT COUNT(*) INTO existing_count FROM mytab WHERE r = r_value AND h = h_value AND p <> p_value;
IF existing_count > 0 THEN
  CONCAT(COALESCE(p_out, ''), E'n Failed for r = ', r_value, ' , h = ', h_value, ' and p = ', p_value);
END IF;
v_query := CONCAT('INSERT INTO mytab (r,h,p) VALUES (',r_value,',',h_value,',', p_value,') ON CONFLICT (r,h) DO NOTHING;');
EXECUTE v_query;
CLOSE c1;
END;

    This is the cursor code only not the procedure.
    Here I have used p_out which will be the out parameter for the stored procedure.
    Initially insert all records in the temp_table.
    upon calling the procedure the out parameter will provide the output with the details of failing rows on each line.

    Login or Signup to reply.
  2. 0

    Create a trigger for the table.

    create or replace function before_insert_on_mytab()
returns trigger language plpgsql as $$
declare
    rec record;
begin
    select *
    from mytab
    where r = new.r and h = new.h
    into rec;
    if rec is not null then
        if rec.p <> new.p then
            -- notice
            raise notice '%', new;
            -- notify
            perform pg_notify('mytab_log', new::text);
            -- log table
            insert into mytab_errors values
            (new.r, new.h, new.p);
        end if;
        return null;
    end if;
    return new;
end $$;

    The trigger function filters inserted rows and reports incorrect ones as a notice, or channel notification, or in a special table (your choice).

    Test it in db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search