Suggestion for Prepending Leading Zeroes for Incremental Record IDs

Support, Troubleshooting, & Help
1

Looking for alternatives to creating leading zeroes for record IDs numerical suffixes (i.e. the 00001 in CHI-TLD-00001). Currently pulling in the last record, splitting off the numerical component, converting text to number, incrementing, then a bunch of else-if logic to add the right number of zeros when turning it back into text, depending on the numerical value).

I’m hoping for a simple formatting expression trick, such as the TEXT(target, format) function in Excel…

2

There is at least one better solution.

  1. make a string of as many zeros as long the number should be (e.g. 5)
    '00000'

  2. add your counter
    '00000' + @variable.counter

  3. take the amount of digits from the right, so you cut the unnecessary zeros
    right('00000' + @Variable.counter , 5)

  4. add your prefix
    'CHI-TLD-' + right('00000' + @Variable.counter , 5)

image

2 Likes
3

I too wouldn’t mind the ability to choose to have serialized records during table creation. A good default since the underlying table is postgres would be serial8 datatype (bigserial + constraint + autoincrement).

That being said, if you’re depending on serialized records I would look at the architecture of your application. If there’s no way for the ID column to be meaningful, I use the random option in an expression when creating records just to ensure they’re unique.

1 Like