Is there a good reason to insert HEX as BYTEA in PostgreSQL?

Question

I found this from the documentation:

... binary strings specifically allow storing octets of value zero and ... octets outside the range 32 to 126 ...

To me that sounds like there is no reason to use BYTEA to store a HEX value? Still a lot of people seem to use BYTEA for sth. like this:

013d7d16d7ad4fefb61bd95b765c8ceb
007687fc64b746569616414b78c81ef1

Is there a good reason to do so?

I think the other question is quite clear in why that may be the right thing to do: when stored as BYTEA `013d` is stored as two bytes, when stored as a string, the same value will be stored as four characters `0`, `1`, `2` and `3`. Ie, more compact and also, if these are actually raw bytes, no useless conversions will need to be applied so it's probably more efficient in the end. Or am I missing your point? — fvu, Sep 13 '18 at 18:04

score 4 · Accepted Answer · answered Sep 14 '18 at 07:36

There are three good reasons:

It will require less storage space, since two hexadecimal digits are stored as one byte.

It will automatically check the value for correctness:

SELECT decode('0102ABCDNONSENSE', 'hex');
ERROR:  invalid hexadecimal digit: "N"

you can store and retrieve binary data without converting them from and to text if your API supports it.

1 Answers1