postgresql - Can't find my mistake - INSERT has more target columns than expressions -

i struggling understand made mistake.

sql table

java code making sql call

what have checked :

• values of parameters

• amount of parameters : i'm correctly trying update of columns of sql table.

• tried code on sql table (with different columns) , works flawlessly.

• rewrote whole sql request twice , i'm still getting error.

exact error message i'm getting (translated):

error : insert has more target columns expressions.  : function pl/pgsql insupd_ref_zone_type_link1(), line 17 @ sql instruction   

thanks reading,

cyborgforever

edit : can't open links, code below :

sql table :

-- table: public.ref_zone_type_link1  -- drop table public.ref_zone_type_link1;  create table public.ref_zone_type_link1 (   modified_by text not null,   modified_from text not null,   modify_date timestamp(6) time zone not null,   zonetype_id bigint not null default nextval('ref_zone_type_link1_zonetype_id_seq'::regclass),   documentsubtype_id bigint not null,   program_level boolean not null default false,   complex_level boolean not null default false,   subcomplex_level boolean not null default false,   lot_level boolean not null default false,   usage_enddate timestamp(6) time zone,   constraint ref_zone_type_link1_pk primary key (zonetype_id, documentsubtype_id),   constraint ref_zone_type_link1_ref_document_subtype_fk foreign key (documentsubtype_id)       references public.ref_document_subtype (documentsubtype_id) match simple       on update no action on delete no action,   constraint ref_zone_type_link1_ref_zone_type_fk foreign key (zonetype_id)       references public.ref_zone_type (zonetype_id) match simple       on update no action on delete no action ) (   oids=false ); alter table public.ref_zone_type_link1   owner buildingmgr;  -- trigger: before_delrow_ref_zone_type_link1 on public.ref_zone_type_link1  -- drop trigger before_delrow_ref_zone_type_link1 on public.ref_zone_type_link1;  create trigger before_delrow_ref_zone_type_link1   before delete   on public.ref_zone_type_link1   each row   execute procedure public.del_ref_zone_type_link1();  -- trigger: before_insupdrow_ref_zone_type_link1 on public.ref_zone_type_link1  -- drop trigger before_insupdrow_ref_zone_type_link1 on public.ref_zone_type_link1;  create trigger before_insupdrow_ref_zone_type_link1   before insert or update   on public.ref_zone_type_link1   each row   execute procedure public.insupd_ref_zone_type_link1(); 

java code :

public static void update(string author_ip, string author_login, double date,          integer zonetype_id, integer documentsubtype_id, boolean program_level, boolean complex_level,         boolean subcomplex_level, boolean lot_level, string usage_enddate) throws sqlexception, myexception {     final logger logger = logmanager.getlogger(refzonetypelink1.class);     connection conn = dbutils.getdbconnectionname("buildingmgr");     if (conn == null){         logger.error("refzonetypelink1 method : update\n"+messages.geterrormessage("conn_fail"));         throw new myexception("conn_fail");     }     mystatement statement_action;     string sqlorder = "update ref_zone_type_link1 set modified_by = ?, modified_from = ?, modify_date = to_timestamp(?), "             + " program_level = ?, complex_level = ?, subcomplex_level = ?, lot_level = ?, usage_enddate = ? "             + " zonetype_id = ? , documentsubtype_id = ?";     try {         statement_action = new mystatement(conn.preparestatement(sqlorder));         statement_action.setstring(1, author_login);         statement_action.setstring(2, author_ip);         statement_action.setdouble(3, date);         statement_action.setboolean(4, program_level);         statement_action.setboolean(5, complex_level);         statement_action.setboolean(6, subcomplex_level);         statement_action.setboolean(7, lot_level);         if (usage_enddate != "") {             try {                 statement_action.setdate(8, new java.sql.date(new simpledateformat("dd/mm/yyyy").parse(usage_enddate).gettime()));             } catch (parseexception e) {                 logger.error("refbuildingownergroup method : create\n"+e.getlocalizedmessage());                 statement_action.setnull(8, types.date);             }         } else {             statement_action.setnull(8, types.date);         }         statement_action.setint(9, zonetype_id);         statement_action.setint(10, documentsubtype_id);         statement_action.executeupdate();     } catch (sqlexception e) {         dbutils.deldbconnection(conn);         logger.error("refzonetypelink1 method : update\n"+e.getlocalizedmessage());         throw e;     }     dbutils.deldbconnection(conn); }   

as asked, insupd_ref_zone_type_link1 function.

for can't open link, code below :

-- function: public.insupd_ref_zone_type_link1()  -- drop function public.insupd_ref_zone_type_link1();  create or replace function public.insupd_ref_zone_type_link1()   returns trigger $body$ begin     if (new.modified_by='')         new.modified_by=null;     end if;     if (new.modified_from='')         new.modified_from=null;     end if;     if (tg_op='update' , new.modify_date >= now())         raise exception 'modify_date must inf now()';     end if;     if (tg_op = 'update' , new.modify_date < old.modify_date , (new.modified_by != old.modified_by or new.modified_from != old.modified_from))         raise exception 'data modified after user:%,time:%',old.modified_by,to_char(old.modify_date,'dd/mm/yyyy hh:mi:ss');     end if;     new.modify_date:=now();     if (tg_op = 'update')         insert ref_zone_type_archive          values (new.modify_date,new.modified_by,new.modified_from,old.*);     end if;     return new;   end;   $body$   language plpgsql volatile   cost 100; alter function public.insupd_ref_zone_type_link1()   owner buildingmgr; 

last edit guess :

as vao tsun pointed out, because wrong table updated. ref_zone_type_archive instead of ref_zone_type_link1_archive

editing function solved problem.

i didn't checked part, didn't create database.

it works flawlessly now, !