Column policy¶
The Column Policy defines if a table enforces its defined schema or if it’s allowed to store additional columns which are a not defined in the table schema.
If the column policy is not defined within the with
clause, strict
will
be used.
strict
¶
The column policy can be configured to be strict
, rejecting any column on
insert/update/copy_to which is not defined in the schema.
Example:
cr> create table my_table (
... title text,
... author text
... ) with (column_policy = 'strict');
CREATE OK, 1 row affected (... sec)
If you try to insert using a column not specified in the table schema, CrateDB will raise an error.
cr> insert into my_table (new_col) values(1);
ColumnUnknownException[Column new_col unknown]
dynamic
¶
The other option is dynamic
. dynamic
means that new columns can be
added using insert
, update
or copy from
.
Note that adding new columns to a table with a dynamic
policy will affect
the schema of the table. Once a column is added, it shows up in the
information_schema.columns
table and its type and attributes are fixed. It
will have the type that was guessed by its inserted/updated value and they will
be analyzed as plain
with the plain analyzer,
which means as-is.
Note
The data type of the new column is guessed from the data type of the value provided in the insert statement that creates the column.
If a new column a
was added with type boolean
, adding strings to this
column will result in an error, except the string can be implicit casted to a
boolean
value.
Here’s an example:
cr> create table my_table (
... title text,
... author text
... ) with (column_policy = 'dynamic');
CREATE OK, 1 row affected (... sec)
Now, you can do inserts that add columns:
cr> insert into my_table (new_col) values(1);
INSERT OK, 1 row affected (... sec)
Which will update the table schema:
cr> show create table my_table;
+-----------------------------------------------------+
| SHOW CREATE TABLE doc.my_table |
+-----------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "doc"."my_table" ( |
| "title" TEXT, |
| "author" TEXT, |
| "new_col" BIGINT |
| ) |
| CLUSTERED INTO 4 SHARDS |
...
New columns added to dynamic tables are identical to regular columns. You can retrieve them, sort by them and use them in where clauses.
Warning
The mapping update is processed asynchronously on multiple nodes. If a new
field gets added to the local mapping of two shards, these shards are
sending their mapping to the master. If this mapping update gets delivered
later than the next query on the previously added column, it will result in
a ColumnUnknownException
.