What is the datatype bytea and when would I use it?

Question

In Postgres there is a datatype called bytea

The Postgres docs are here for it: http://www.postgresql.org/docs/9.0/static/datatype-binary.html

I cannot understand when I would ever use this - nor can I really understand the purpose of this datatype.

I have come across this term bytea several times and starting to wonder to myself "It seems like they expect me to understand this... Maybe I should find out what it is."

What is a simple definition for it and some circumstances of when I would possibly use it?

Solution 1

I think the documentation is reasonably clear on the differences between bytea and text:

Binary strings are distinguished from character strings in two ways. First, binary strings specifically allow storing octets of value zero and other "non-printable" octets (usually, octets outside the range 32 to 126). Character strings disallow zero octets, and also disallow any other octet values and sequences of octet values that are invalid according to the database's selected character set encoding. Second, operations on binary strings process the actual bytes, whereas the processing of character strings depends on locale settings. In short, binary strings are appropriate for storing data that the programmer thinks of as "raw bytes", whereas character strings are appropriate for storing text.

http://www.postgresql.org/docs/9.0/static/datatype-binary.html

... it has to do with whether the contents are "text" (subject to the locale and internationalizations settings you've applied to your server configuration and the OS on which you're running it) vs. arrays of "octets" (sequences of 8-bit binary values --- commonly referred to as "bytes").

(There are some technical distinctions between the term "byte" and the term "octet" -- because, historically, some platforms and computing devices used "bytes" with parity and/or stop bits while the term "octets" always means exactly 8-bits; a term that was introduced to clarify specifications and documentation for networking protocols).

Solution 2

VARBINARY, which is an equivalent to BYTEA in Postgres, can be used to store JWT access tokens.

create table oauth_access_token (
  token_id VARCHAR(255),
  token BYTEA,
  .......
)