| Updated: April 19, 2023 |
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.
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 point here is that the type is recommended, not required. Any column can still store any type of data; however, 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 a QDB database is assigned one of the following type affinities:
A column with TEXT affinity stores all data using the storage classes NULL, TEXT, and BLOB. If numerical data is inserted into a TEXT column, it's converted to text form before being stored.
A column with NUMERIC affinity may contain values using all five storage classes. If 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 succeeds, 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 class. No attempt is made to convert NULL or BLOB storage classes.
A column with INTEGER affinity behaves 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's converted to an integer and stored using the INTEGER class.
A column that uses the NONE affinity doesn't prefer one storage class over another and makes no attempt to coerce data before it's inserted.