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, !


Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more