Column affinity

In QDB, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called manifest typing.) All other SQL databases engines that we are aware of use the more restrictive system of static typing where the type is associated with the container, not the value.

In order to maximize compatibility between QDB and other database engines, QDB supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The key here is that the type is recommended, not required. Any column can still store any type of data, in theory. It's just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its affinity.

Each column in an QDB database is assigned one of the following type affinities:

A column with TEXT affinity stores all data using the storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity, it is converted to text form before being stored.

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it's stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class. If the conversion can't be performed, the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB storage classes.

A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted, it is converted to an integer and stored using the INTEGER storage class.

A column with affinity NONE doesn't prefer one storage class over another. It makes no attempt to coerce data before it's inserted.