Problems with creating a postgresql trigger function to create a modified entry for every insert statement

Question

So my first question here on SO, let me describe the setup: I have a postgressql database (version 12) with a table guilds (containing an internal guild_id and a few other informations). The guild_id is used as foreign key for many other tables like a teams table. Now if a team is inserted in teams for another guild then the guild with the guild_id = 1, I want a trigger function to create the same team entry, but now with a modified guild_id (should be now 1).

Definition of the relevant things I have atm:

create table if not exists bot.guilds
(
    guild_id         bigserial not null
        constraint guilds_pk
            primary key,
    guild_dc_id      bigint    not null,
);
create table if not exists bot.teams
(
    team_id       bigserial   not null
        constraint teams_pk
            primary key,
    guild_id      bigserial      not null
        constraint teams_guilds_guild_id_fk
            references bot.guilds
            on delete cascade,
    team_name     varchar(20) not null,
    team_nickname varchar(10) not null
);

alter table bot.teams
    owner to postgres;

create unique index if not exists teams_guild_id_team_name_uindex
    on bot.teams (guild_id, team_name);

create unique index if not exists teams_guild_id_team_nickname_uindex
    on bot.teams (guild_id, team_nickname);

create function duplicate_teams() returns trigger
    language plpgsql
as
$$
BEGIN
    INSERT INTO bot.teams VALUES(1,NEW."team_name",NEW."team_nickname");
RETURN NEW;
END;
$$;

create trigger duplicate_team
    after insert
    on bot.teams
    for each row
execute procedure bot.duplicate_teams();

If I try now to insert a new row in teams (INSERT INTO bot.teams ("guild_id", "team_name", "team_nickname")VALUES (14, 'test2', 'test2');), I get the following error message (orginial german, translated by me to english):

[42804] ERROR: Column »guild_id« has type integer, but the expression has the type character varying
HINT: You have to rewrite the expression or cast the value.
WITH: PL/pgSQL-function duplicate_teams() row 3 in SQL-expressions

After execution the orgininal insert statement isn't in the table neither the copy. I tried to cast the values for the guild id to serial, integer, bigserial.. but everytime the same error. I'm confused by the error message part with "has the type character varying".

So my questions are:

Is my understanding correct, that the error is caused by the trigger? and due to the error in the trigger the original insert statement doesnt work too?
Why is the type varing even with a cast?
Where is the error in the code?

I tried to search for the problem, but found nothing helpfull. Any hints are welcome. Thank you for your help!

EDIT: The answer from @Lukas Thaler works, but now I get a new error:

[23505] ERROR: doubled key value violates unique-constraint »teams_guild_id_team_name_uindex«
Detail: Key»(guild_id, team_name)=(1, test3)« exists already.
WHERE: SQL-Statement»INSERT INTO bot.teams(guild_id, team_name, team_nickname)  VALUES(1,NEW."team_name",NEW."team_nickname")«
PL/pgSQL-Function duplicate_teams() row 3 in SQL-Statement
SQL-Statment »INSERT INTO bot.teams(guild_id, team_name, team_nickname)  VALUES(1,NEW."team_name",NEW."team_nickname")«
PL/pgSQL-Function duplicate_teams() row 3 in SQL-Statement

But the table only contains only "3,11,TeamUtils,TU"...

Notes: a) serial are in Don't Do this, b) Are you trying to INSERT INTO bot.teams AFTER INSERT ON bot.teams? — Alex Yu
– Alex Yu, Commented Mar 17, 2021 at 10:26
Referring to your edit: can you try to do select currval('bot.teams_guild_id_seq')? As @AlexYu mentioned, SERIALS can be weird at times. Given the fact that this is a foreign key, you are probably better off converting bot.teams.guild_id to a BIGINT, anyways (I don't think auto-incrementing is the intended behaviour in the child table) — Lukas Thaler
– Lukas Thaler, Commented Mar 17, 2021 at 10:37

Lukas Thaler · Accepted Answer · 2021-03-17 10:24:25Z

1

bot.teams has four columns: team_id, guild_id (both numerical data types), team_name and team_nickname (both varchars). In your INSERT statement in the function definition, you only provide three values and no association to particular columns. The default is to insert them in order, which assigns 1 to team_id and (crucially) NEW."team_name" to guild_id, hence the insert fails with a type mismatch error.

Specifying

INSERT INTO bot.teams(guild_id, team_name, team_nickname)  VALUES(1,NEW."team_name",NEW."team_nickname");

in your function should resolve your problem

To answer your other questions:

The INSERT statement is being executed inside a transaction, and a failure in the trigger will cause the entire transaction to be aborted and rolled back, hence you don't see the original row inserted to the table, either
The type is not deviating from the cast, it was the wrong value being inserted that caused the data type mismatch

edited Mar 17, 2021 at 10:24

answered Mar 17, 2021 at 10:02

Lukas Thaler

2,7205 gold badges19 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Doluk Over a year ago

Oh damm, I'm stupid. Now another error appears :). Thank you for your help so far!

Alex Yu Over a year ago

Can you please explain why 1 is passed to guild_id? It looks strange for me. Is it some kind of serial behaviour?

Lukas Thaler Over a year ago

The 1 (like all other inserted values) is taken from OPs function definition in their post. I don't know what their intent is, there

Collectives™ on Stack Overflow

Problems with creating a postgresql trigger function to create a modified entry for every insert statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related