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

AutoPK not allowing insert/update to a Schema because it is saying it duplicated

Avatar

Avatar
Validate 1
Level 1
TokyoTye
Level 1

Likes

4 likes

Total Posts

10 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 1
TokyoTye
Level 1

Likes

4 likes

Total Posts

10 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
TokyoTye
Level 1

25-09-2019

I have a file import, that is working in Stage 1 that we use for Dev Testing, I have moved it to Stage 3 for UAT, but when the File import gets to the insert/update step I am presented with an error that says

09/25/2019 11:05:43 AMPGS-220000 PostgreSQL error: ERROR:  duplicate key value violates unique constraint "mtnpassdetail_id" DETAIL:  Key (ipassdetailid)=(6123) already exists.

This isn't the only one there are several like this with a different ID number. 

We have the Auto PK as well as a self identified PK from our data set.  This is the way Adobe set up them when we had out implementation. 

They also advised us to go into Admin>ACX>Config>SQL Scripts> Mtn Custom Sequence (Mtn is our namespace we have for our custom schema's) The advised that the following would make it so that they do not run out of Primary Key numbers.  This one below was set up already for us. 

SELECT CreateSequenceIfNecessary('MtnPassDetailSeq', '1000', 'cache 1');

create or replace function getnewMtnPassDetailSeqs(integer) returns text as '

declare strIds text; i integer; iId integer;

begin strIds = '''';

for i in 1 .. $1 loop select NextVal(''MtnPassDetailSeq'') into iId ;

if i > 1 then strIds := strIds || '','';

end if;

strIds := strIds || cast(iId as text);

end loop;

return strIds;

end;

'

language plpgsql;

I did make a change to the Data Schema we have where I added a column, and it is working correctly in Stage 1 so not sure what this AutoPK issue is in Stage 3.

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

976 likes

Total Posts

1,096 posts

Correct Reply

514 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

976 likes

Total Posts

1,096 posts

Correct Reply

514 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

26-09-2019

Hi,

Use generic query editor to select max(id) from the table, then set the sequence to that value.

Thanks,

-Jon

Answers (0)