Column policy

The Column Policy controls whether a table strictly enforces its predefined schema or allows the addition of new columns on the fly.

If column_policy is not explicitly set in the WITH clause, CrateDB uses the default value: strict.

---

Column Policy Options

1. strict (default)

When set to strict, CrateDB rejects any attempt to insert, update, or copy data into columns that are not defined in the table schema.

Example:

CREATE TABLE my_table (
    title TEXT,
    author TEXT
) WITH (column_policy = 'strict');

If you try to insert into a column that doesn’t exist:

INSERT INTO my_table (new_col) VALUES (1);
-- ERROR: ColumnUnknownException[Column new_col unknown]

---

2. dynamic

When set to dynamic, CrateDB automatically adds new columns when they are first referenced in an INSERT, UPDATE, or COPY FROM statement.

How it works:

• The new column is added to the table schema and becomes visible in information_schema.columns.

• Its data type is inferred from the first value inserted.

• It is analyzed using the plain analyzer (no tokenization, stored as-is).

• Once created, the column behaves like any other — you can filter, sort, and aggregate on it.

Important: The inferred data type is fixed after creation. If the first inserted value for a column is a BOOLEAN, inserting a STRING later will fail unless it can be implicitly cast to a boolean.

Example:

CREATE TABLE my_table (
    title TEXT,
    author TEXT
) WITH (column_policy = 'dynamic');

INSERT INTO my_table (new_col) VALUES (1);

Now the table schema includes new_col:

SHOW CREATE TABLE my_table;

Output:

CREATE TABLE IF NOT EXISTS "doc"."my_table" (
    "title" TEXT,
    "author" TEXT,
    "new_col" BIGINT
)
CLUSTERED INTO 4 SHARDS
WITH (
    column_policy = 'dynamic',
    number_of_replicas = '0-1'
)

---

Behavior Notes

• Schema Evolution: In dynamic mode, schema changes happen automatically on insert/update. This can be convenient during exploration but may cause schema sprawl if not managed carefully.

• Data Type Guessing:

  • First inserted value determines the column type.

  • Type changes are not allowed afterwards.

• Analyzer: New columns always use the plain analyzer by default.

• Concurrency Warning: Mapping updates are processed asynchronously across nodes. If two shards add the same new column at the same time, and a query is run before the mapping syncs, a ColumnUnknownException may occur temporarily.

---

When to Use Each Policy

Policy	Best for	Trade-offs
strict	Well-defined, stable schemas; production workloads where schema changes are rare.	No accidental columns, but schema updates require ALTER TABLE.
dynamic	Flexible data ingestion, prototyping, exploratory analytics.	Risk of uncontrolled schema growth; inferred types may be unexpected.

---

💡 Tip: You can change the column policy of an existing table with:

ALTER TABLE my_table SET (column_policy = 'strict');