3

I am providing a key table to my users in various categories so I am thinking how to TRIGGER/RETURNING the function with each SELECT. Functions to generate shorthand unique IDs

  1. The thread answer Generating human-readable/usable, short but unique IDs but no salt so no.
  2. http://hashids.org/ can be a valid option as proposed by the answer here
  3. Any default PostgreSQL options?

Example of key table where I need the keys

Module 1 
1. <key1>
2. <key2>
3. ...

Module 2 
1. <key1>
2. <key2>

Keys are bound to the SERIAL primary keys in the table event_log. I am thinking if the database can do the generation task for you i.e. how to bind your primary key to any algorithm that can generate such shorthand forms. I have currently the data as the following in the table where SERIAL PRIMARY is not so clear

CREATE TABLE event_log (
    data_id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(10) NOT NULL,
    module INTEGER NOT NULL, - - 1 or 2 or 3 as value
    time TIMESTAMP NOT NULL
);

Idea: Bind a TRIGGER and/or RETURNING nice_id to SELECT * FROM event_log WHERE module=1 queries

OS: Debian Linux 8.7
SQL: PostgreSQL 9.4 and/or R sqldf

3
  • 1
    "I am providing a key table to my users in various categories" I don't understand what you're doing. What is a key table? In your example what is a module table. Telling me 1. key 1 doesn't tell me what the "1" is or what the "key" is. May 28, 2017 at 18:58
  • @EvanCarroll Key table is just a list of identifiers to the cases. May 29, 2017 at 4:56
  • If the cases have identifiers, why is there is a key table? May 29, 2017 at 5:03

2 Answers 2

3

I am thinking if the database can do the generation task for you i.e. how to bind your primary key to any algorithm that can generate such shorthand forms

Since you mention a salt, I assume you're looking for short strings that are hard to guess or reproduce by an outsider, despite being originated with a database sequence. The permuteseq extension essentially does that.

The function that produces a short string from an integer has to be provided, for instance:

CREATE FUNCTION id_to_alpha(n int) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
 alphabet text:='abcdefghijklmnopqrstuvwxyz012345678';
 sign_char char:='9';
 base int:=length(alphabet); 
 _n bigint:=abs(n);
 output text:='';
BEGIN
 LOOP
   output := output || substr(alphabet, 1+(_n%base)::int, 1);
   _n := _n / base;
   EXIT WHEN _n=0;
 END LOOP;
 RETURN CASE WHEN (n<0) THEN output || sign_char::text ELSE output END;
 RETURN output;
END; $$;

Then you may set the short id slug in a BEFORE INSERT trigger, like this:

CREATE EXTENSION permuteseq;

CREATE TABLE things( 
 id serial,
 short_id text,
 name varchar(10) not null
);

-- keep the sequence short for short output strings
ALTER SEQUENCE things_id_seq MAXVALUE 1000000;

CREATE FUNCTION generate_short_id() RETURNS TRIGGER AS $$
DECLARE
 secret bigint := 123456789;  -- change for a different secret sequence
BEGIN
 NEW.short_id := id_to_alpha(range_encrypt_element(NEW.id, 1, 10000000, secret)::int);
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER things_trigger BEFORE INSERT ON things 
FOR EACH ROW EXECUTE PROCEDURE generate_short_id();

Testing it:

INSERT INTO things(name) SELECT 'foo' FROM generate_series(1,10);
SELECT * FROM things;
 id | short_id | name 
----+----------+------
  1 | wf3hg    | foo
  2 | tm6lg    | foo
  3 | riqbg    | foo
  4 | p6jp     | foo
  5 | h3r3c    | foo
  6 | 3sx5d    | foo
  7 | w8ecd    | foo
  8 | km3le    | foo
  9 | llt1e    | foo
 10 | xwtxc    | foo

In this example, there is both id and short_id in the table, but id is technically redundant with short_id. You could create a sequence outside of the table without the SERIALsyntax like this, and not even have id in the table:

CREATE SEQUENCE seq_pk MAXVALUE 1000000;

CREATE TABLE things2(
 short_id TEXT 
    DEFAULT id_to_alpha(permute_nextval('seq_pk'::regclass, 123456::bigint)::int) 
    PRIMARY KEY,
 name text
);

INSERT INTO things2(name) values('foo');

SELECT * FROM things2;
 short_id | name 
----------+------
 znyh     | foo

If you can't use permuteseq, as an alternative still based on encrypting the ID, you may look at the plpgsql implementation of SKIP32, or XTEA for 64-bit (bigint) which won't require a compilation or being superuser. The drawback is that you cannot fine-tune the size of the output by changing the sequence's range.

1
  • how is this safe? if request B gets the same value a A before request A has committed then you'll have a collision right?
    – ChatGPT
    Nov 25, 2020 at 9:25
2

You can just use a sequence, base64 codify the result, and use it as your key.

The concept is, given an awful_id, turn it into something a bit easier for humans (although we might not agree on whether 310276431 is easier or more difficult than MzEwMjc2NDMx):

 SELECT
     awful_id, 
     replace(encode(decode(awful_id::text || 'x', 'escape'), 'base64'), '=', '') AS "nicer_id??"
 FROM 
     generate_series(1, 1000, 13) as s(awful_id) ;

This would generate ...

 awful_id | nicer_id??
 -------: | :---------
        1 | MXg       
       14 | MTR4      
       27 | Mjd4      
       40 | NDB4      
[...]
      924 | OTI0eA    
      937 | OTM3eA    
      950 | OTUweA    
      963 | OTYzeA    
      976 | OTc2eA    
      989 | OTg5eA    

You would then define your tables without a serial primary key, but based on a serial number:

You create a sequence for every table that needs a synthetic primary key:

CREATE SEQUENCE t_sq ;

You define the PK of your table based on this sequence, but encoded:

CREATE TABLE t
(
    nice_id char(10) 
        PRIMARY KEY 
        DEFAULT replace(encode(decode(nextval('t_sq') || 'x', 'escape'), 'base64'), '=', ''),
    more_data text
) ;

You would insert data into it as if your nice_id were a serial:

 INSERT INTO 
     t (more_data)
 VALUES
     ('a'),
     ('b'),
     ('c'),
     ('d')
 RETURNING
     t.* ;
 nice_id    | more_data
 :--------- | :--------
 MXg        | a        
 Mng        | b        
 M3g        | c        
 NHg        | d        

dbfiddle here

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.