ERROR: column "semester_id" is of type integer but expression is of type boolean

Question

I created this PL/pgSQL trigger function in PostgreSQL:

CREATE or replace FUNCTION public.trigger31()
        RETURNS trigger
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE NOT LEAKPROOF 
    AS $BODY$
    begin
        if(TG_OP='INSERT') then
            if(New.start_date>(select max(end_date) from "Semester") and New.end_date>New.start_date) then

                Insert Into "Semester" (semester_id,academic_year,academic_season,start_date,end_date,semester_status)
                values (New.semester_id=(select max(s.semester_id)+1 from "Semester" s):: integer,
                        new.academic_year= academic_year_trig(),
                        new.academic_season=academic_season_trig(),
                        new.start_date,new.end_date,
                        new.semester_status=semester_stat_trig()
                    );
                return NEW;
            else
                RAISE EXCEPTION 'Invalid start_date or end_date';
            end if;
        end if;
    end;
$BODY$;

Trying to execute it, I get this error:

ERROR:  column "semester_id" is of type integer but expression is of type boolean
LINE 2:    values (New.semester_id=(select max(s.semester_id)+1 from...

I am not sure why. I cast the expression of New.semester_id and I have the same error. Also tried and this:

New.semester_id=(select max(s.semester_id):: integer +1 from "Semester" s)

and I still had the same error.

`Insert Into "Semester" (semester_id ,,,, values (New.semester_id=(select max(s.semester_id)+1 from "Semester" s)` is prone to race condition why not using SERIAL on the semester_id column? — Raymond Nijland, Apr 29 '18 at 13:08
The `CREATE TRIGGER` statement should also be in the question. And *always* your version of Postgres. — Erwin Brandstetter, Apr 29 '18 at 14:17
not i tried this.But it's ok mr Erwin solved my problem.Sorry guys next time i will try to expound better my question. — Apostolos Lazidis, Apr 29 '18 at 14:40

Erwin Brandstetter · Accepted Answer · 2018-04-29T14:40:52.977

Seems like you are mixing INSERT and UPDATE syntax by mistake. It might work like this:

...
            INSERT INTO "Semester" (semester_id, academic_year, academic_season, start_date, end_date, semester_status)
            VALUES ((select max(s.semester_id) + 1 from "Semester" s),
                    academic_year_trig(),
                    academic_season_trig(),
                    new.start_date,
                    new.end_date,
                    semester_stat_trig()
                   );
...

But then I suspect you also misunderstood how triggers work. If that trigger function is for a trigger ON INSERT on table "Semester", it would trigger an infinite loop - besides being nonsense, generally. Read the fine manual about triggers and trigger functions.

All that aside, seems like you need to start by reading about serial and IDENTITY columns:

Auto increment table column

thank you very match it works now,but you are right.this trigger is an infinite loop :/ — Apostolos Lazidis, Apr 29 '18 at 14:37

ERROR: column "semester_id" is of type integer but expression is of type boolean

1 Answers1