Auto increment an ID if other ID exists

[Anonymous]

Dear experts, 

I would like to do an auto increment on a field in function of what I receive. 

If my couple : car_id & sequence_id already exists. I would like to increment sequence_id

For ex : I would like to have : 

car_id        sequence_id (by auto increment)

1                 1 

2                 1

2                 2

3                 1

4                 1

4                 2

4                 3

4                 4

Hope I am clear.

Thank you for your help. Best regards. 

Romain

[TRF]

Hi,

Here it is:

Fields are all declared as Integer.

Hope this helps.

[Anonymous]

Dear TRF,

Thank you for your answer. 

I do the same formula as you. It works fine I have : 

car_id                sequence_id

1                           1

2                           1

3                           1

4                           1

But, for the 2nd run, when I want to insert (not update) in my database, I have the error message that I have a duplicate keys (so the increment doesn't work in this case). 

Please find copies of my error

Thank you for your help. 

Regards

2017-11-16_102928.jpg
2017-11-16_104133.jpg

[TRF]

Hi,

This case wasn't explain in your 1rst post.

3 possibilities:

- store locally (on a file for example) the current sequence value for each car_id and reuse it as a sequence start value for the next run

- get the max sequence_id for each car_id when the job starts and reuse it as a sequence start value

- get the max sequence_id for the current car_id using a tMap with lookup to query the db for each input row

Does this helps?

[Anonymous]

Hi, 

I'm sorry if I wasn't clear. 

I think the 3rd solution is the best in my case. 

Hum, correct me if I'm wrong  

In tPostgresqlRow, 

select max(sequence_id) from car_temp where source_car_id = database_car_id 

case max(sequence_id)

        when not null then max(sequence_id) + 1

        else max(sequence_id)

end

Regards

[TRF]

select max(sequence_id) where source_car_id = database_car_id 
case max(sequence_id)
        when not null then max(sequence_id) + 1
        else 1
end

change the else clause.

[Anonymous]

Hi,

My clause doesn't work : 

UPDATE car_temp SET sequence_id = 
(case (select max(car_staging.sequence) from car_staging,car_temp 
where car_temp.car_id = car_staging.car_id)
       when not null then 
                (select max(car_staging.sequence) from car_staging , car_temp where 
                 car_temp.car_id = car_staging.car_id + 1)
        else (1)
end)

And I have this error : 

ERROR: operator does not exist: integer = boolean
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

[TRF · Accepted solution]

Don't know what you want to do with that SQL update.

Read this for an explaination of how to work with lookup in tMap (search for "Read at each Row" in the page, this is what you need if I understand your case).

[Anonymous]

Ok, I try to do your 3rd method but by sql query. 

Hum so, forget my previous message, can you detail your 2nd and 3rd method please (which component ...)

How can I get the max(sequence_id) in my source db ? 

Please find my job

2017-11-16_135239.jpg

[TRF]

Refer to the PostgreSQL document but I think you need to select nextval to be able of select currval (that's not what you want).

So you need to select the max value from the table, not the sequence.

All details for solution 3 are included in the link I gave you in the previous post - havec you read id yet?

The better way is to try by yourself.