CODEWITHSUNDEEP
postgresqlpostgresql-9.6
user1602492
user1602492

Reputation:

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

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?

Upvotes: 1

Views: 2223

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248215

There are three good reasons:

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

  2. It will automatically check the value for correctness:

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

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

Upvotes: 6

Related Questions