PostgreSQL Binary String (bytea) Functions

Using text `||` concatenation on hex strings or casting binary to text to assemble binary data is error-prone and encoding-fragile. PostgreSQL's bytea `||` operator handles binary concatenation natively and correctly.

✓ Instead: Use `'\\xDEAD'::bytea || '\\xBEEF'::bytea` for binary concatenation rather than joining hex text strings and re-casting.

`bit_length(b) = 8 * octet_length(b)`. When you need the byte count for size checks, calling `bit_length` and dividing by 8 is needlessly indirect and invites off-by-8 errors.

✓ Instead: Use `octet_length(col)` directly to get byte count instead of `bit_length(col) / 8`.

`btrim(data, '\\x0102'::bytea)` removes any combination of bytes 0x01 and 0x02 from both ends — not the literal sequence `\x0102`. This over-trims when those byte values appear legitimately at the payload boundary.

✓ Instead: Use `substring` with known fixed offsets to strip exact-length headers/trailers rather than `btrim` when the boundary bytes also appear inside valid payload.

Passing malformed base64 or hex input to `decode()` raises an exception that aborts the transaction. In bulk import pipelines this can silently roll back many rows. User-supplied encoded strings must be validated before decoding.

✓ Instead: Wrap `decode()` in a `CASE WHEN col ~ '^[0-9a-fA-F]+

Encoding binary content to hex or base64 before storing in a text column wastes space (hex doubles size, base64 adds ~33%), loses binary operators, and forces decode on every read. The native `bytea` type stores binary efficiently and supports direct operations.

✓ Instead: Store binary data in a `bytea` column and use `encode(col, 'hex')` only at the API boundary for display or transmission.

`get_byte(data, 0)` returns the first byte. Using index 1 returns the second byte — a systematic off-by-one error when parsing protocol frames. The confusion worsens because `substring(data FROM 1 ...)` is 1-based.

✓ Instead: Always use `get_byte(data, 0)` for the first byte. Add a comment clarifying 0-based indexing, especially alongside `substring` calls which use 1-based FROM.

`ltrim(data, mask)` greedily removes any byte in the mask set from the left — not a literal prefix sequence. If the leading valid payload bytes happen to match the mask, they get silently stripped too.

✓ Instead: Use `substring(data FROM N+1)` to skip exactly N leading bytes rather than `ltrim` when the prefix bytes also occur at the start of valid content.

MD5 is cryptographically broken — collision and preimage attacks are feasible. Using md5 for password hashing, integrity checks on security-critical data, or digital signatures is unsafe regardless of how it is encoded.

✓ Instead: Use `sha256(data::bytea)` (built-in since PG 11) or pgcrypto's `digest(data, 'sha256')` for any security-sensitive hash. Reserve md5 only for non-security use cases like ETags or deduplication keys.

`length(str)` on text counts characters (code points), not bytes. For multibyte UTF-8 strings like emoji or CJK characters, `length` silently underestimates the byte size, letting oversized values past byte-limit checks.

✓ Instead: Use `octet_length(col) <= N` for all byte-budget validations (network buffers, wire protocols). Reserve `char_length(col)` for UI display-character limits.

If you omit `FOR count`, `overlay` replaces exactly as many bytes as the replacement string contains. If the replacement is longer or shorter than the target region, the total length of the bytea result changes silently, breaking fixed-frame binary protocols.

✓ Instead: Always specify `FOR count` explicitly when patching binary frames: `overlay(data PLACING new_val FROM offset FOR length_of_region)`.

`position(sub IN bytes)` always returns an integer — 0 when the subsequence is not found, never NULL. Testing `IS NOT NULL` is always true and finds nothing useful. This silently passes all rows through when filtering for presence.

✓ Instead: Use `WHERE position('\\xFF'::bytea IN data) > 0` to test for presence, not `IS NOT NULL`.

`rtrim(data, mask)` greedily removes any byte in the mask set from the right. If valid payload bytes at the end happen to be in the mask, they are also stripped, silently corrupting the binary value.

✓ Instead: Use `substring(data FOR octet_length(data) - N)` to strip exactly N trailing bytes rather than `rtrim` when those byte values appear in the content.

`set_byte` requires a newvalue in [0, 255]. Passing a value outside this range raises an error at runtime. There is no silent wrapping or truncation — validation must happen in application code before the call.

✓ Instead: Validate the byte value before calling: `WHERE new_val BETWEEN 0 AND 255`. Also remember set_byte is 0-indexed; `set_byte(data, 0, v)` modifies the first byte, not the second.

Storing `encode(sha256(data), 'hex')` as text uses 64 bytes per hash. Storing the raw `sha256(data)` bytea uses 32 bytes. The text form also requires a `decode` on lookup, making index comparisons more complex.

✓ Instead: Store `sha256(data)` directly in a `bytea` column. Use `encode(sha256_col, 'hex')` only for display: `WHERE sha256_col = decode($1, 'hex')`.

`substring(data FROM 1 ...)` uses 1-based positioning. `get_byte(data, 0)` uses 0-based positioning. When parsing the same binary frame with both functions, mixing conventions causes persistent off-by-one errors that are hard to debug.

✓ Instead: Use `substring` consistently for multi-byte extractions (1-based) and `get_byte` for single bytes (0-based). Add comments at call sites to make the indexing convention explicit.