Mongo to Postgres wrap - convert mongo hexa id field to shorter integer

Your best option is to migrate MongoDB fields ObjectId

to PostgreSQL columns uuid

. Note that the UUID has more bytes, so you need to fill in the values.

More about this:

• POSTGRESQL: auto-increment for a varchar field

If you really want to use bigint

s, you have two options:

1. Creation of completely new values

• create your schema (with tables, constraints, etc.)
  • in this schema use text
    
    / varchar
    
    for your values ObjectId
    
    (for now)
• create foreign keys for all your relationships, ON UPDATE CASCADE
  
  for all columns ObjectId
  
  .
• create sequences for all tables that have columns ObjectId
  
  .

• update columns ObjectId
  
  (while they are still text
  
  / varchar
  
  ):

  UPDATE table_name
  SET    object_id_col = nextval('table_name_object_id_col_seq')::text
  
        
  
          
          
          
        
  
      

  (This will propagate changes to table references since foreign keys were set earlier.)

• remove foreign keys
• change the column types of those columns ObjectId
  
  tobigint
  
  
• change your sequences as OWNED BY
  
  a table column
• change tables to use nextval('table_name_object_id_col_seq')
  
  as defaults
• re-add foreign keys

This method is guaranteed to never call duplicate values ​​during migration. And the sequence can be used to create new values ​​for the primary keys.

2. Use your original values ​​in any way

Truncation will result in loss of information, so you might end up with duplicate values ​​no matter which method you try. However, you can reduce the likelihood of this by using f.ex. bitwise XOR

(usually an operator #

in PostgreSQL) instead of a module.

With this function f.ex. you can use your original values ​​like:

• start with 0
  
  (or with another, correct the initial value)
• with each iteration use N number of least significant bits from the input
• calculate the result as <the_previous_result> # <value_from_2.>
  
  
• continue in 2.when there are more unused bits (input should be old but N least significant)

Here's a SQL function that does it:

create or replace function hex_xor(p_hex text, p_bits int default 64, p_default bigint default 0)
  returns bigint
  language sql
  immutable
as $func$
  with recursive r as (
      select ('x' || p_hex)::varbit h, p_default r, 0 i
    union all
      select case
               when bit_length(h) <= p_bits then varbit ''
               else substring(h for bit_length(h) - p_bits)
             end,
             r # case
               when bit_length(h) <= p_bits then h::bit(64)::bigint
               else substring(h from bit_length(h) - p_bits + 1 for p_bits)::bigint
             end,
             i + 1
      from   r
      where  bit_length(h) > 0
  )
  select   r
  from     r
  order by i desc
  limit    1
$func$;

      

        
        
        
      

    

This assumes that the parameter is p_hex

indeed in hex, and the parameter is p_bits

never larger 64

.

But , if you just use it as it is, you may find yourself later, with conflicting values ​​on INSERT

. What you can do is f.ex. use:

select -hex_xor('56c4100560b2d8308f4bde21', 63)

      

        
        
        
      

    

when migrating. So migrated ObjectId

will always be negative, and subsequent generated primary keys (f.ex. from sequence) will always be positive.

http://rextester.com/RCVFN77368