Re: Problem with "on conflict"

> On Mar 26, 2025, at 12:15 PM, Udo Polder <udo.polder@gmail.com> wrote:
>
> Hi all. I have some really strange behavior with postgres and 'on conflict'.
> I had some error today at a customer and i figured out, the problem is triggered by postgres doing an insert on a
tablewith wrong data (not even provided), when it should insert the data. 
>
> After some playing around with the query, the error just went away, magically ….
> Here you can see, that the insert is updating stuff:
>
> There also is a trigger on the db:
>
> CREATE OR REPLACE FUNCTION fn_hut_bundle_create_id() returns TRIGGER AS $$
> begin
>     if new.bundle_id is null or new.bundle_id='' then
>         NEW.bundle_id = concat('HB-', nextval('seq_hut_bundle_id'));
>     end if;
>
>     return NEW;
> END;
> $$ LANGUAGE plpgsql;
> create trigger tr_hut_bundle_id before insert on hut_bundle for each row EXECUTE FUNCTION fn_hut_bundle_create_id();
>
> <ti600gsoOaUj04Ng.png>
>
>
>
> now working:
> <U4iCI1t7CX28e3oM.png>
>
> Can someone give me some hint, what the problem could be? After my „playing“ the error can not be reproduced any
longer,and the statement is inserting stuff like it should. 
> i played with null and '' as a primary key(bundle_id)
>
> Postgres is:
> PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110,
64-bit
>
> Any help would be very welcome ....
>
> Thanks
>

To me it looks like the record already existed and the on conflict update occurred.  Given that hut_id is not updated
onconflict; the old value remained.  Was the sequence recycled or was 'HB-32’ created manually beforehand?