Base36 Conversion in PostgreSQL

While these functions suited my needs, I’m not entirely certain that this is the best approach. Anytime you’re performing looping operations from within the database, you really ought to think hard as to whether there’s a better solution. I haven’t profiled these functions, but if you’re planning to make extensive use of them in a production environment, I’d recommend that you do so. Additionally, it could be argued that shifting the base of an integer is a presentational thing and ought to be done at the application-level anyhow.

CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0)
  RETURNS varchar AS $$
        DECLARE
			chars char[];
			ret varchar;
			val bigint;
		BEGIN
		chars := ARRAY['0','1','2','3','4','5','6','7','8','9'
			,'A','B','C','D','E','F','G','H','I','J','K','L','M'
			,'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
		val := digits;
		ret := '';
		IF val < 0 THEN
			val := val * -1;
		END IF;
		WHILE val != 0 LOOP
			ret := chars[(val % 36)+1] || ret;
			val := val / 36;
		END LOOP;

		IF min_width > 0 AND char_length(ret) < min_width THEN
			ret := lpad(ret, min_width, '0');
		END IF;

		RETURN ret;
 
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE OR REPLACE FUNCTION base36_decode(IN base36 varchar)
  RETURNS bigint AS $$
        DECLARE
			a char[];
			ret bigint;
			i int;
			val int;
			chars varchar;
		BEGIN
		chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
 
		FOR i IN REVERSE char_length(base36)..1 LOOP
			a := a || substring(upper(base36) FROM i FOR 1)::char;
		END LOOP;
		i := 0;
		ret := 0;
		WHILE i < (array_length(a,1)) LOOP		
			val := position(a[i+1] IN chars)-1;
			ret := ret + (val * (36 ^ i));
			i := i + 1;
		END LOOP;
 
		RETURN ret;
 
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

7 Replies to “Base36 Conversion in PostgreSQL”

  1. May I use this in some commercial work I’ve been doing? If so may I make changes if attribution is given?

  2. Certainly. You may use it in anyway you wish; no attribution is necessary. Though, as I mentioned in the original posting, you may want to test the performance of the functions or maybe even reconsider whether this is a task that should be done within the DB.

  3. Would you have a C equivalent of this? Doing this in pl/pgsql is going to be a major hit.

    Thanks!

  4. Your base36_decode contains bug in string:

    ret := ret + (val * (36 ^ i));

    For large numbers (for example,
    select * from base36_decode(‘324adc6a6ds’));

    it returns
    ERROR: invalid input syntax for integer: “1.11836923491136e+16”
    CONTEXT: PL/pgSQL function “base36_decode” line 17 at assignment

    Mentioned string must be fixed as follows:
    ret := ret + (val * (36 ^ i))::bigint;

    After that all works ok, thanks for sharing your code.

  5. Another limitation of this code is – it supports only half of possbible bigint values (only positive ones). When ret overflows – function returns exception.

Leave a Reply

Your email address will not be published. Required fields are marked *