Auto-increment text primary key in postgres

Question

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.

Answer 1

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; 

Answer 2

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.