Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

How do i get auto incremental key from my sequence?

gowthamanrajendran
Level 2
Level 2

I am trying to execute insert operation throught javascript method. 

 

var temp="INSERT INTO gowSampleDB(sSTG_ID,sATTRB_CD,sATTRB_VAL) VALUES ('9383677898998939','GOWTHAMANTEST','GOWTHAMTEST1')";
logInfo("My query :"+temp);
var strSql = sqlExec(temp);

 

 

but its throwing unique constraint exception. How do i refer to my sequence and get the next value to insert?

 

02/11/2020 12:50:57 PM WDB-200001 SQL statement 'INSERT INTO gowSampleDB(sSTG_ID,sATTRB_CD,sATTRB_VAL) VALUES ('9383677898998939','GOWTHAMANTEST','GOWTHAMTEST1')' could not be executed.


02/11/2020 12:50:57 PM PGS-220000 PostgreSQL error: ERROR: duplicate key value violates unique constraint "gowsampledb_id" DETAIL: Key (isampledbid)=(0) already exists.

1 Accepted Solution
Milan_Vucetic
Correct answer by
Community Advisor
Community Advisor

Hi @gowthamanrajendran ,

 

if your table has autopk defined as below (check table schema), you do not need to put it in your query, it will be increased automatically.

milanv69354962_0-1581494685154.png

If you are using your own column as primary key, then database will thrown an error if you try to import value which already exist.

Check as well, if you have unique index on some column.

 

Regards,

Milan

 

View solution in original post

5 Replies
Milan_Vucetic
Correct answer by
Community Advisor
Community Advisor

Hi @gowthamanrajendran ,

 

if your table has autopk defined as below (check table schema), you do not need to put it in your query, it will be increased automatically.

milanv69354962_0-1581494685154.png

If you are using your own column as primary key, then database will thrown an error if you try to import value which already exist.

Check as well, if you have unique index on some column.

 

Regards,

Milan

 

View solution in original post

gowthamanrajendran
Level 2
Level 2

Hi, I have autopk turned on but still its throwing error, <element autopk="true" label="SampleDB" name="SampleDB" pkSequence="testSchemaSeq">

i dont have my own column as primary key. 

 

gowthamanrajendran_0-1581522117927.png

 

Milan_Vucetic
Community Advisor
Community Advisor

Hi @gowthamanrajendran,

 

if your table won't have huge number of records remove pkSequence="testSchemaSeq", update database structure and try again. You shouldn't insert manual values for id. Those may collide with the next value from the associated sequence. Provide an explicit list of target columns (which is almost always a good idea for persisted INSERT statements) and omit autoincremental columns completely from your INSERT statement.

 

Regards,

Milan

InderM
Level 2
Level 2

Hi,

You can do it in schema, please check in schema, it should be configured like below

<element autopk="true" name="<yourUniqueFieldName>"
pkSequence="<yourSequenceName>" >

 

In javascript, you need to define it like below

var temp="INSERT INTO gowSampleDB(sSTG_ID,sATTRB_CD,sATTRB_VAL) VALUES ( yourSequenceName.NEXTVAL,'GOWTHAMANTEST','GOWTHAMTEST1')";
logInfo("My query :"+temp);
var strSql = sqlExec(temp);

 

Let me know if it works.

 

Regards,

gowthamanrajendran
Level 2
Level 2
No i tried this. this is throwing another exception. missing from clause ....