SQL general: New default column sizes for string and binary data types (Postponed)¶

Note

This behavior change was part of the 2025_07 bundle, but the change has been postponed. The change will be introduced in a future bundle. The change is not available for testing.

When this behavior change bundle is enabled, the default sizes for string and binary data type change:

Before the change:

The default size for text string data types was 16 MB.

The default size for binary data types was 8 MB.

After the change:

The default size for text string data types is 128 MB.

The default size for binary data types is 64 MB.

Before this change, DDL statements could explicitly specify sizes larger than 16 MB for text string columns and 8 MB for binary columns, but the defaults were 16 MB and 8 MB, respectively, when no size was specified.

After this behavior change, the default size is 128 MB for text string columns and 64 MB for binary columns when no size is specified in DDL statements. So, INSERT statements can insert values larger than 16 MB into text string columns and larger than 8 MB into binary columns without explicitly specifying larger sizes.

Note

This change doesn’t affect DDL statements for Apache Iceberg™ tables and user-defined functions (UDFs) because they already use the larger default sizes.

The change applies to columns of the VARCHAR data type and to columns of data types that are synonymous with VARCHAR, such as STRING, except for data types with a default size of 1 (including CHAR, CHARACTER, and NCHAR). The change also applies to columns of the BINARY data type and to columns of data types that are synonymous with BINARY, such as VARBINARY.

For example, the following statement creates a table without specifying maximum sizes for the columns:

CREATE TABLE test_new_default_sizes (
  name VARCHAR,
  profile_image BINARY);

Run the following query to show the maximum sizes of the columns:

DESCRIBE TABLE test_new_default_sizes
  ->> SELECT "name", "type" FROM $1;

Before the behavior change, the query returns the following output:

+---------------+-------------------+
| name          | type              |
|---------------+-------------------|
| NAME          | VARCHAR(16777216) |
| PROFILE_IMAGE | BINARY(8388608)   |
+---------------+-------------------+

After the behavior change, the query returns the following output:

+---------------+--------------------+
| name          | type               |
|---------------+--------------------|
| NAME          | VARCHAR(134217728) |
| PROFILE_IMAGE | BINARY(67108864)   |
+---------------+--------------------+

Views and materialized views can inherit large default sizes¶

In some cases, when you create a view or a materialized view that uses expressions in column definitions, the columns inherit the new default sizes, even if the columns in the source table explicitly specify smaller sizes.

For example, create a source table that explicitly sets the maximum size for a column of VARCHAR data type to 16777216:

CREATE TABLE test_default_size_source_table (
  id INTEGER,
  description VARCHAR(16777216));

Create a view and a materialized view based on this table without using expressions in the column definitions:

CREATE VIEW test_default_size_view AS
SELECT id, description FROM test_default_size_source_table;

CREATE MATERIALIZED VIEW test_default_size_mv AS
SELECT id, description FROM test_default_size_source_table;

Run the following queries to show the maximum sizes of the columns:

DESCRIBE VIEW test_default_size_view
  ->> SELECT "name", "type" FROM $1;

DESCRIBE MATERIALIZED VIEW test_default_size_mv
  ->> SELECT "name", "type" FROM $1;

Both before and after the change, these queries return the following output:

+-------------+-------------------+
| name        | type              |
|-------------+-------------------|
| ID          | NUMBER(38,0)      |
| DESCRIPTION | VARCHAR(16777216) |
+-------------+-------------------+

Create a view and a materialized view based on the source table and use expressions in the column definitions:

CREATE VIEW test_default_size_view_with_exp AS
SELECT description || RANDSTR(10, 1) AS col
  FROM test_default_size_source_table;

CREATE MATERIALIZED VIEW test_default_size_mv_with_exp AS
SELECT description || RANDSTR(10, 1) AS col
  FROM test_default_size_source_table;

Run the following queries to show the maximum sizes of the columns:

DESCRIBE VIEW test_default_size_view_with_exp
  ->> SELECT "name", "type" FROM $1;

DESCRIBE MATERIALIZED VIEW test_default_size_mv_with_exp
  ->> SELECT "name", "type" FROM $1;

Before the behavior change, these queries return the following output:

+------+-------------------+
| name | type              |
|------+-------------------|
| COL  | VARCHAR(16777216) |
+------+-------------------+

After the behavior change, these queries return the following output:

+------+--------------------+
| name | type               |
|------+--------------------|
| COL  | VARCHAR(134217728) |
+------+--------------------+

Tables created using CREATE TABLE AS SELECT can inherit large default sizes¶

In some cases, when you create a table using a CREATE TABLE AS SELECT (CTAS) statement that uses expressions in column definitions, the columns inherit the new default sizes, even if the columns in the source table explicitly specify smaller sizes.

For example, create a source table that explicitly sets the maximum size for VARCHAR and BINARY columns:

CREATE TABLE test_default_size_ctas_source_table (
  small_text VARCHAR(1000),
  medium_text VARCHAR(50000),
  large_text VARCHAR(16777216),
  binary_data BINARY(1000000));

Use a CTAS statement to create a table from this source table:

CREATE TABLE test_default_size_ctas AS
SELECT small_text,
       medium_text,
       large_text || RANDSTR(10, 1) AS processed_text,
       binary_data
  FROM test_default_size_ctas_source_table;

In this example, the column definition for the processed_text column uses an expression.

Run the following queries to show the maximum sizes of the columns:

DESCRIBE TABLE test_default_size_ctas
  ->> SELECT "name", "type" FROM $1;

Before the behavior change, the query returns the following output, and the processed_text column shows the smaller default size:

+----------------+-------------------+
| name           | type              |
|----------------+-------------------|
| SMALL_TEXT     | VARCHAR(1000)     |
| MEDIUM_TEXT    | VARCHAR(50000)    |
| PROCESSED_TEXT | VARCHAR(16777216) |
| BINARY_DATA    | BINARY(1000000)   |
+----------------+-------------------+

After the behavior change, the query returns the following output, and the processed_text column shows the larger default size:

+----------------+--------------------+
| name           | type               |
|----------------+--------------------|
| SMALL_TEXT     | VARCHAR(1000)      |
| MEDIUM_TEXT    | VARCHAR(50000)     |
| PROCESSED_TEXT | VARCHAR(134217728) |
| BINARY_DATA    | BINARY(1000000)    |
+----------------+--------------------+

Ref: 2118