I'm writing code for a data warehouse that extracts information from SalesForce. The format of the SalesForce IDs is like so:
Where each digit is a base-62 value denoted by:
a-z (26 values, case-sensitive)
A-Z (26 values, case-sensitive)
0-9 (10 values)
26+26+10 = a total of 62 values per digit
I want to convert these into a format that can be efficiently indexed by Postgres for thousands of rows.
I have gotten this far:
select id, array_to_string(array_agg(ascii),'','0')::decimal FROM ( SELECT id, ascii(regexp_split_to_table(id, '')) from example_schema.example_table ) x group by id
but it seems super inefficient and also may be hard to reverse in some cases because there's no delimiter between the codes.
- Is using a
decimaltype as an ID/identity efficient?
- Is there an easier way to convert these to a numeric value that can still be used as a primary key and is a reversible operation if needed?
Here's the query plan:
GroupAggregate (cost=109862.92..130267.92 rows=742000 width=51) Group Key: account.id -> Sort (cost=109862.92..111717.92 rows=742000 width=23) Sort Key: account.id -> Result (cost=0.00..14875.99 rows=742000 width=23) -> ProjectSet (cost=0.00..3745.99 rows=742000 width=51) -> Seq Scan on account (cost=0.00..30.42 rows=742 width=19)
001n000000ShgGbAAJ 746565987110310483484848484848110494848 001n000000SIZE7AAP 4848491104848484848488373906955656580 001n000000Sj3NCAAZ 48906565677851106834848484848481104948 001n000000SJK1sAAH 48484911048484848484883747549115656572
They seem out of order which will cause issues with reversing the operation