Base58 Encoder function in PostgreSQL

Question

I found this MySQL function for Base58 Encoder in a Github Gist.

DELIMITER $$

CREATE FUNCTION base58_encode (num int) RETURNS varchar(255)
  DETERMINISTIC

BEGIN
  DECLARE alphabet varchar(255);
  DECLARE base_count int DEFAULT 0;
  DECLARE encoded varchar(255);
  DECLARE divisor DECIMAL(10,4);
  DECLARE mode int DEFAULT 0;

  SET alphabet = '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  SET base_count = CHAR_LENGTH(alphabet);
  SET encoded = "";

  WHILE num  >= base_count DO
    SET divisor = num / base_count;
    SET mode = (num - (base_count* TRUNCATE(divisor,0)));
    SET encoded = CONCAT(SUBSTRING(alphabet FROM mode+1 FOR 1), encoded);
    SET num = TRUNCATE(divisor,0);
  END WHILE;

  SET encoded = CONCAT(SUBSTRING(alphabet FROM num+1 FOR 1), encoded);

  RETURN (encoded);

END

I am new to PostgreSQL and having difficulty converting above function to PostgreSQL function.

How would be the equivalent PostgreSQL function of above SQL snippet for Base58 Encoder?

What have you try, where you have problem? Because this should be solve easy reading the manual — Juan Carlos Oropeza, Jan 13 '16 at 02:28
Hey @JuanCarlosOropeza, thanks for reminding about the "Manual". Posted PostgreSQL version. http://stackoverflow.com/a/34759277/1433665. Thanks! — TheKojuEffect, Jan 13 '16 at 05:52

score 5 · Accepted Answer · answered Jan 13 '16 at 05:50

5

Equivalent function in PostgreSQL that I came up with is as follows.

CREATE FUNCTION base58_encode(num INT)
  RETURNS VARCHAR(255) AS $encoded$

DECLARE
  alphabet   VARCHAR(255);
  base_count INT DEFAULT 0;
  encoded    VARCHAR(255);
  divisor    DECIMAL(10, 4);
  mod        INT DEFAULT 0;

BEGIN
  alphabet := '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  base_count := char_length(alphabet);
  encoded := '';

  WHILE num >= base_count LOOP
    divisor := num / base_count;
    mod := (num - (base_count * trunc(divisor, 0)));
    encoded := concat(substring(alphabet FROM mod + 1 FOR 1), encoded);
    num := trunc(divisor, 0);
  END LOOP;

  encoded = concat(substring(alphabet FROM num + 1 FOR 1), encoded);

  RETURN (encoded);

END; $encoded$
LANGUAGE PLPGSQL;

answered Jan 13 '16 at 05:50

TheKojuEffect

20,103
19
89
125

There is a missing `l` inside the small letters! – Jan Drábek Sep 18 '20 at 11:00
@JanDrábek `l` is left out so that it isn't confused with `1`. `I`, `O` etc are also left out. – TheKojuEffect Sep 18 '20 at 15:13
Oh I see... In that case I would suggest adding a comment as I spent an hour debugging why this code provides different output than my PHP code... – Jan Drábek Sep 20 '20 at 05:04
2

I believe Jan Drábek made a confusion between base64 and base58. But even for people really looking at base58, be careful since the alphabet in above function is not the usual one associated with base58. In https://tools.ietf.org/id/draft-msporny-base58-01.html the alphabet is 123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz with capital letters first. – pba Aug 18 '21 at 08:17

score 2 · Answer 2 · answered Mar 01 '21 at 16:17

For completeness here's a quick and dirty swipe at the inverse base58_decode() function:

CREATE OR REPLACE FUNCTION base58_decode(str VARCHAR(255))
  RETURNS BIGINT AS $$
DECLARE
  alphabet VARCHAR(255);
  c CHAR(1);
  p INT;
  v BIGINT;
BEGIN
  alphabet := '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  v := 0;
  FOR i IN 1..char_length(str) LOOP
    c := substring(str FROM i FOR 1);
    -- This is probably wildly inefficient, but we're just using this function for diagnostics...
    p := position(c IN alphabet);
    IF p = 0 THEN
      RAISE 'Illegal base58 character ''%'' in ''%''', c, str;
    END IF;
    v := (v * 58) + (p - 1);
  END LOOP;
  RETURN v;
END;$$
LANGUAGE PLPGSQL;

score 1 · Answer 3 · answered Sep 07 '22 at 10:25

I've modified a sample for the Postgres 9.x above. It's proved to work with large numbers (blockchain addresses, for example)

CREATE OR REPLACE FUNCTION base58_encode (num numeric)
RETURNS text AS
$body$
declare
  --alphabet text = '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  alphabet text[] = array[
    '1','2','3','4','5','6','7','8','9', 
    'A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z',
    'a','b','c','d','e','f','g','h','i','j','k','m','n','o','p','q','r','s','t','u','v','w','x','y','z'
   
  ];
  cnt integer = 58;
  dst text = '';
  _mod numeric;
begin
  while (num >= cnt) loop
    _mod = num % cnt;
    num = (num - _mod) / cnt;
    dst = alphabet[_mod+1] || dst;
  end loop;
  return alphabet[num+1] || dst;
end;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
COST 100;

score 1 · Answer 4 · edited May 15 '23 at 19:39

Here is a function to convert a uuid to base58. Since postgres can't generally handle the BigInt conversion from UUID, you can use an extension to put the JS directly in postgres:

CREATE EXTENSION IF NOT EXISTS plv8;

CREATE OR REPLACE FUNCTION uuid_to_base58(uuid uuid)
  RETURNS text AS $$
    const base58Alphabet = '123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz';
    let bigintValue = BigInt('0x' + uuid.toString().replace(/-/g, ''));
    let base58 = '';
    while (bigintValue > BigInt(0)) {
        const remainder = bigintValue % BigInt(base58Alphabet.length);
        bigintValue /= BigInt(base58Alphabet.length);
        base58 = base58Alphabet.charAt(Number(remainder)) + base58;
    }
    return base58;
$$ LANGUAGE plv8 IMMUTABLE;

Good for short IDs where you could convert back and forth:

d0269693-c9e5-4a0d-998e-a2e1579accc3  --->  Sho37CJBR5FqrV4FXPWhGN

score 0 · Answer 5 · answered Apr 05 '18 at 11:57

Postgres 9.x

CREATE OR REPLACE FUNCTION base58_encode (num bigint)
RETURNS text AS
$body$
declare
  --alphabet text = '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  alphabet text[] = array[
    '1','2','3','4','5','6','7','8','9', 
    'a','b','c','d','e','f','g','h','i','j','k','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
    'A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z'
  ];
  cnt integer = 58;
  dst text = '';
  mod integer;
begin
  while (num >= cnt) loop
    num = num / cnt;
    mod = num % cnt + 1;
    dst = alphabet[mod] || dst;
  end loop;

  return alphabet[num] || dst;
end;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

There are 2 bugs in this function: 1. lines "num = num / cnt; mod = num % cnt + 1;" must be swapped. 2. "return alphabet[num] || dst;" must be changed to "return alphabet[num + 1] || dst;" — pba, Aug 18 '21 at 08:12

score 0 · Answer 6 · answered May 14 '21 at 19:57

To continue the completeness, if you need to decode to a UUID (Int16) I built on @mike-blackwell's answer to do that. Gist

I would love some feedback and improvements. I have to imagine theres a better way to go from a 128 bit numeric to a UUID

Code:

CREATE OR REPLACE FUNCTION base58_decode(encoded_id VARCHAR(22))
    RETURNS UUID AS $$
DECLARE
    -- Bitcoin base58 alphabet
    alphabet CHAR(58) := '123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz';
    c CHAR(1) := null;
    p INT := null;
    raw_num NUMERIC := 0;
    uuid_str VARCHAR(32);
BEGIN
    /*
    Parses a UUID encoded with the Bitcoin base58 standard
    Use sparingly, any application connecting to the database should handle decoding the ID itself
    */

    -- Decode id to numeric
    FOR i IN 1..CHAR_LENGTH(encoded_id) LOOP
        c = SUBSTRING(encoded_id FROM i FOR 1);
        p = POSITION(c IN alphabet);
        raw_num = (raw_num * 58) + (p - 1);
    END LOOP;

    -- Parse NUMERIC into bytes
    -- There must be a better way to go from a NUMERIC -> UUID
    uuid_str := '';
    FOR i IN 0..31 LOOP
        uuid_str = CONCAT(uuid_str, TO_HEX(MOD(raw_num, 16)::INT));
        raw_num = DIV(raw_num, 16);
    END LOOP;

    return REVERSE(uuid_str)::UUID;
END;$$
LANGUAGE PLPGSQL;

Base58 Encoder function in PostgreSQL

6 Answers6

Linked