Sequence ID incrementing even on discarded creations : Portal

T

Titouan Roussel

started a topic about 2 years ago

Hello , i have a problem when adding new data in the user interface : when I click on create , it automatically create a new ID ( the id is set to sequence) . However , when i discard the creation , it keep the incrementation in the ID sequence and when i redo the action of creating , the ID is incremented again.

It cause my tables to have holes in the ID sequence and mess with my import

Is there a way to discard the incrementation when changes are discarded or is this a bug ?

thank

Best Answer

T

Toshish Chauhan said about 2 years ago

Here is Function which help us to get latest id From gd_table and we increment in this and return value in Id Field. It works even we discard the creation.

-- DROP FUNCTION semarchy_emp_tutorial5.get_coummnication_id();

CREATE OR REPLACE FUNCTION semarchy_emp_tutorial5.get_coummnication_id()
  RETURNS int4
  LANGUAGE plpgsql
AS $function$
declare mk int4;
  BEGIN

    select  cm.communication_id into mk from  semarchy_emp_tutorial5.gd_communication cm order by
      cm.communication_id desc limit 1;
    
  case when mk is null then mk=0;
       else -- do nothing
       end case;
  return mk+1;
  END;
$function$
;

Here We call the function in SemQL Section for ID generation (Declare function with same name in model after this it will be available for use in model). you can try this .

T

Toshish Chauhan

said about 2 years ago

Hello ,

This this the expected behaviour of a sequence from database. The incrementation in the ID sequence is due to the sequence is called from Database . Whenever we click on Create, the sequence is called and id is Incremented from the old value and appears in id field. It is not possible to discard the increment in sequence when changes are discarded.

you can choose manual creation of id to provide ids of your choice.

T

Titouan Roussel

said about 2 years ago

Is there a way to have auto incrementing ID that don't increment on discard ? Maybe an SemQL expression ?

T

Toshish Chauhan

said about 2 years ago

Answer

Here is Function which help us to get latest id From gd_table and we increment in this and return value in Id Field. It works even we discard the creation.

-- DROP FUNCTION semarchy_emp_tutorial5.get_coummnication_id();

CREATE OR REPLACE FUNCTION semarchy_emp_tutorial5.get_coummnication_id()
  RETURNS int4
  LANGUAGE plpgsql
AS $function$
declare mk int4;
  BEGIN

    select  cm.communication_id into mk from  semarchy_emp_tutorial5.gd_communication cm order by
      cm.communication_id desc limit 1;
    
  case when mk is null then mk=0;
       else -- do nothing
       end case;
  return mk+1;
  END;
$function$
;

Here We call the function in SemQL Section for ID generation (Declare function with same name in model after this it will be available for use in model). you can try this .