The data type
uuid is perfectly suited for the task. It only occupies 16 bytes as opposed to 37 bytes in RAM for the
text representation. (Or 33 bytes on disk, but the odd number would require padding in many cases to make it 40 bytes effectively.) And the
uuid type has some more advantages.
SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;
You might consider other (a bit cheaper) hashing functions if you don't need the cryptographic component of md5, but I would go with md5 for your use case. md5 is well established, very fast and your values are mostly read-only anyway.
A word of warning: For your case (
immutable once written) a functionally dependent (pseudo-natural) PK is fine. But the same would be a pain where updates on
text are possible. Think of correcting a typo: the PK and all depending indexes, FK columns in "dozens of other tables" and other references would have to change as well. Table and index bloat, locking issues, slow updates, lost references, ...
text can change in normal operation, a surrogate PK would be a better choice. I suggest a
bigserial column with a range of -9223372036854775808 to +9223372036854775807. That's nine quintillion two hundred twenty-three quadrillion three hundred seventy-two trillion thirty-six something billion) distinct values for "billions of rows". Might be a good idea in any case: 8 instead of 16 bytes for dozens of FK columns and indexes!). Or a random UUID for much bigger cardinalities or distributed systems. You can always store said md5 (as
uuid) additionally to find rows in the main table from the original text quickly.
For your query, see:
What about hyphens?
If you prefer a representation without hyphens, remove the hyphens for display:
SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')
But I wouldn't bother. The default representation is just fine. And the problem's really not the representation here.
If other parties should have a different approach and throw strings without hyphens into the mix, that's no problem, either. Postgres accepts several reasonable text representations as input for a
uuid. The manual:
PostgreSQL also accepts the following alternative forms for input: use
of upper-case digits, the standard format surrounded by braces,
omitting some or all hyphens, adding a hyphen after any group of four
digits. Examples are:
md5() function returns
text. You would use
decode() to convert to
bytea and the default representation of that is:
SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')
You would have to
encode() again to get the original text representation:
SELECT encode(my_md5_as_bytea, 'hex');
To top it off, values stored as
bytea would occupy 20 bytes in RAM (and 17 bytes on disk, 24 with padding) due to the internal
varlena overhead, which is particularly unfavorable for size and performance of simple indexes.
What about "invalid" UUIDs?
There are no "invalid" UUIDs.
Octet 13 and 17 encode a "version" and "variant" for certain UUID types. But Postgres'
uuid data type accepts all 128-bit quantities without regard to "version" or "variant". That's according to RFC 4122:
Apart from determining whether the timestamp portion of the UUID
is in the future and therefore not yet assignable, there is no
mechanism for determining whether a UUID is 'valid'.
"Version" and "variant" are meaningless / not applicable for this use case. To verify I ran a quick test:
Everything works in favor of a