> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# BYTES

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `BYTES` <InternalLink path="data-types">data type</InternalLink> stores binary strings of variable length.

## Aliases

In CockroachDB, the following are aliases for `BYTES`:

* `BYTEA`
* `BLOB`

## Syntax

To express a byte array constant, see the section on
<InternalLink path="sql-constants#byte-array-literals">byte array literals</InternalLink> for more
details. For example, the following three are equivalent literals for the same
byte array: `b'abc'`, `b'\141\142\143'`, `b'\x61\x62\x63'`.

In addition to this syntax, CockroachDB also supports using
<InternalLink path="sql-constants#string-literals">string literals</InternalLink>, including the
syntax `'...'`, `e'...'` and `x'....'` in contexts where a byte array
is otherwise expected.

## Size

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, <InternalLink path="architecture/storage-layer">write amplification</InternalLink> and other considerations may cause significant performance degradation.

We **strongly recommend** adding size limits to all <InternalLink path="indexes">indexed columns</InternalLink>, which includes columns in <InternalLink path="primary-key">primary keys</InternalLink>.

Values exceeding 1 MiB can lead to <InternalLink path="architecture/storage-layer">storage layer write amplification</InternalLink> and cause significant performance degradation or even <InternalLink path="cluster-setup-troubleshooting#out-of-memory-oom-crash">crashes due to OOMs (out of memory errors)</InternalLink>.

To add a size limit using <InternalLink path="create-table">`CREATE TABLE`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE name (first STRING(100), last STRING(100));
```

To add a size limit using <InternalLink path="alter-table#alter-column">`ALTER TABLE ... ALTER COLUMN`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET enable_experimental_alter_column_type_general = true;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE name ALTER first TYPE STRING(99);
```

<Tip>
  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.
</Tip>

## Example

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```

## Supported conversions

`BYTES` values can be
<InternalLink path="data-types#data-type-conversions-and-casts">cast</InternalLink> explicitly to
<InternalLink path="string">`STRING`</InternalLink>. This conversion always succeeds. Two
conversion modes are supported, controlled by the
<InternalLink path="set-vars#supported-variables">session variable</InternalLink> `bytea_output`:

* `hex` (default): The output of the conversion starts with the two
  characters `\`, `x` and the rest of the string is composed by the
  hexadecimal encoding of each byte in the input. For example,
  `x'48AA'::STRING` produces `'\x48AA'`.

* `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 `\NNN` otherwise. For example, `x'48AA'::STRING`
  produces `'0\252'`.

`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 `\` and `x`
  (e.g., `\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.

### `STRING` vs. `BYTES`

While both `STRING` and `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](https://emojipedia.org/snowman/)):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT length('☃'::string);
```

```
  length
+--------+
       1
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT length('☃'::bytes);
```

```
  length
+--------+
       3
```

In this case, <InternalLink path="functions-and-operators">`LENGTH(string)`</InternalLink> measures the number of Unicode code points present in the string, whereas <InternalLink path="functions-and-operators">`LENGTH(bytes)`</InternalLink> 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 `STRING` vs. `BYTES`

A literal entered through a SQL client will be translated into a different value based on the type:

* `BYTES` give a special meaning to the pair `\x` at the beginning, and translates the rest by substituting pairs of hexadecimal digits to a single byte. For example, `\xff` is equivalent to a single byte with the value of 255. For more information, see <InternalLink path="sql-constants#string-literals-with-character-escapes">SQL Constants: String literals with character escapes</InternalLink>.
* `STRING` does not give a special meaning to `\x`, so all characters are treated as distinct Unicode code points. For example, `\xff` is treated as a `STRING` with length 4 (`\`, `x`, `f`, and `f`).

## See also

<InternalLink path="data-types">Data Types</InternalLink>