BYTES data type stores binary strings of variable length.
In CockroachDB, the following are aliases for
To express a byte array constant, see the section on
byte array literals for more
details. For example, the following three are equivalent literals for the same
In addition to this syntax, CockroachDB also supports using
string literals, including the
x'....' in contexts where a byte array
is otherwise expected.
The size of a
BYTES value is variable, but it's recommended to keep values under 1 MB to ensure adequate performance. Above that threshold, write amplification and other considerations may cause significant performance degradation.
To add a size limit using
CREATE TABLE name (first STRING(100), last STRING(100));
To add a size limit using
ALTER TABLE ... ALTER COLUMN:
SET enable_experimental_alter_column_type_general = true;
ALTER TABLE name ALTER first TYPE STRING(99);
If your application requires large binary input in single queries, you can store the blobs somewhere your client can access them (using a cloud storage service, for example), and then reference their addresses from a statement.
> CREATE TABLE bytes (a INT PRIMARY KEY, b BYTES); > -- explicitly typed BYTES literals > INSERT INTO bytes VALUES (1, b'\141\142\143'), (2, b'\x61\x62\x63'), (3, b'\141\x62\c'); > -- string literal implicitly typed as BYTES > INSERT INTO bytes VALUES (4, 'abc'); > SELECT * FROM bytes;
+---+-----+ | a | b | +---+-----+ | 1 | abc | | 2 | abc | | 3 | abc | | 4 | abc | +---+-----+ (4 rows)
hex(default): The output of the conversion starts with the two characters
xand the rest of the string is composed by the hexadecimal encoding of each byte in the input. For example,
escape: The output of the conversion contains each byte in the input, as-is if it is an ASCII character, or encoded using the octal escape format
\NNNotherwise. For example,
STRING values can be cast explicitly to
BYTES. This conversion
will fail if the hexadecimal digits are not valid, or if there is an
odd number of them. Two conversion modes are supported:
If the string starts with the two special characters
\xAABB), the rest of the string is interpreted as a sequence of hexadecimal digits. The string is then converted to a byte array where each pair of hexadecimal digits is converted to one byte.
Otherwise, the string is converted to a byte array that contains its UTF-8 encoding.
BYTES can appear to have similar behavior in many situations, one should understand their nuance before casting one into the other.
STRING treats all of its data as characters, or more specifically, Unicode code points.
BYTES treats all of its data as a byte string. This difference in implementation can lead to dramatically different behavior. For example, let's take a complex Unicode character such as ☃ (the snowman emoji):
> SELECT length('☃'::string);
length +--------+ 1
> SELECT length('☃'::bytes);
length +--------+ 3
In this case,
LENGTH(string) measures the number of Unicode code points present in the string, whereas
LENGTH(bytes) measures the number of bytes required to store that value. Each character (or Unicode code point) can be encoded using multiple bytes, hence the difference in output between the two.
Translating literals to
A literal entered through a SQL client will be translated into a different value based on the type:
BYTESgive a special meaning to the pair
\xat the beginning, and translates the rest by substituting pairs of hexadecimal digits to a single byte. For example,
\xffis equivalent to a single byte with the value of 255. For more information, see SQL Constants: String literals with character escapes.
STRINGdoes not give a special meaning to
\x, so all characters are treated as distinct Unicode code points. For example,
\xffis treated as a
STRINGwith length 4 (