0

I would like my content to be searchable by users with a unique 6 characters id (i.e. zab4ac, cba884, ...).

I was thinking of creating a id character(6) primary key column, but then I started wondering how to create ids for new rows. They can be random or sequential, it doesn't matter to me, but I was wondering what the best option would be:

  1. For every INSERT, do a SELECT to get the last key, and "increment it"?
  2. or create a random key and verify if it already exists?

Since these seem inefficient (2 queries for every insertion), is there a automated way to do it, like serial, but for characters (000009, 00000a, 00000b, ..., 00000z, 000010, ..., or something more random)? Or should I use serial in the database and convert it to a string for users?

I am looking to see if I am missing an easy and efficient approach to the problem.

6
  • Why is it preferable to use a 6-character alphanumeric string in place of a simple integer number? I mean, you need a good reason to spend time looking for a solution that may not even perform well in the end.
    – mustaccio
    Jun 29, 2023 at 18:42
  • to make the key shorter for the user to type/communicate. An alpha-numerical key gives me more keys/rows for the same number of digits/chars.
    – pascal
    Jun 29, 2023 at 18:43
  • Well, a 6-digit integer is not longer than that, is it. Humans have no trouble operating with 10-digit phone numbers routinely.
    – mustaccio
    Jun 29, 2023 at 18:43
  • but a 6-digit integer gives me 1 million possible rows, a 6 chars string give me 2 billions. If there is no good solution, I will go with integer. I was just wondering if there was an easy efficient solution I didn't know (never hurts to learn something new)
    – pascal
    Jun 29, 2023 at 18:44
  • I don't think it's very practical, and I'd rather stay with bigint. However, it seems you can just have a sequence + function that does conversion between different bases . Base 36 will cover digits and A-Z. Thus 11 (base 10) will be A(base 36), 35(decimal) will be Z(base 36) . Function example , stackoverflow.com/questions/5997241/…
    – a1ex07
    Jun 29, 2023 at 19:06

2 Answers 2

0

If you really want to do something like that, number conversion to different base might be useful and easy to implement. For instance, base 36 number has all digits 0-9 and letters A-Z . It should be pretty easy to implement (there is an example on stack overflow as well: https://stackoverflow.com/questions/5997241/postgresql-is-there-a-function-that-will-convert-a-base-10-int-into-a-base-36-s .
Then you can create a sequence and pass its' nextval as an argument to the function. Assuming you're using this function, it you'll end up with something similar to

create sequence  test_seq_id_1 ;
 
create table test_seq_base36
(
    test_seq_base36_id text default fn_base36_encode(nextval('test_seq_id_1')) not null primary key,
    data               text
);

-- simple test to insert couple hundred rows 
insert into test_seq_base36(data)
select 'Text num '||num from  generate_series(1,300)num;

select * from test_seq_base36; 
0

This used to be very common on database solutions.

  • Where the product id would be 3 characters of the product name and a few digits from its siz.
  • And Client id would be 3 letters of Surname one from first name and random sequential number.

Both constructed in triggers. For instsnce at the hardware store my account number used to be GUPR23. Its much easier to remember thsn a numerical id.

On one of my products I had a similar option. Client and Staff ids were constructed automatically, if the user did not specify one. And if not unique, the user would have to chose another, or the system would make it sequential.

For goods, I gave them the option of above or a soundex code. But I didd not make them unique. At search time if there were duplicates, the system would show them matching ones, and they would select one.

Both schemes imemented in before insert triggers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.