Data types¶

Note

The type that date and datetime objects are mapped to, depends on the CrateDB column type.

Note

When using date or datetime objects with timezone information, the value is implicitly converted to a Unix time (epoch) timestamp, i.e. the number of seconds which have passed since 00:00:00 UTC on Thursday, 1 January 1970.

This means, when inserting or updating records using timezone-aware Python date or datetime objects, timezone information will not be preserved. If you need to store it, you will need to use a separate column.

Note

Inserting timezone-aware datetime objects is supported; the value is converted to a UTC instant on the way in, as outlined above. On the way out, the dialect returns naive datetime objects in UTC by default.

To read values back as timezone-aware datetime objects instead, configure the CrateDB driver’s time_zone argument, for example:

from sqlalchemy import create_engine

engine = create_engine(
    "crate://localhost:4200",
    connect_args={"time_zone": "+0530"},
)

The driver then converts TIMESTAMP columns to timezone-aware datetime objects transparently. See TIMESTAMP conversion with time zone in the driver documentation for the accepted time_zone values.

SQLAlchemy¶

This section documents data types for the CrateDB SQLAlchemy dialect.

Type map¶

The CrateDB dialect maps between data types like so:

CrateDB	SQLAlchemy
boolean	Boolean
byte	SmallInteger
short	SmallInteger
integer	Integer
long	BigInteger
numeric	Numeric
float	Float
float_vector	FloatVector (extension type)
double	Double
timestamp	TIMESTAMP
string	String
character	CHAR
array	ARRAY
object	ObjectType (extension type)
object	`JSON`
object	`JSONB`
array(object)	ObjectArray (extension type)
geo_point	Geopoint and Geoshape (extension type)
geo_shape	Geopoint and Geoshape (extension type)
ip	`IP` (extension type)
uuid	UUID

Reflection resolves a column whose CrateDB type is missing from this map to UnresolvedType, and emits an SAWarning naming the type and the column. Such a column can still be selected, but rendering it into DDL raises a CompileError.

Note

UUID renders CrateDB’s UUID type, supported by CrateDB since 6.2. The portable Uuid type keeps storing 32 hex digits in a CHAR(32) column.

Note

NUMERIC and DECIMAL name the same type, rendered into the DDL as NUMERIC(precision, scale). CrateDB stores up to 38 digits, and requires the precision, so declaring a column without one raises a compile error rather than emitting DDL the server would reject. Giving a precision but no scale means a scale of zero, as it does in standard SQL, so Numeric(10) holds whole numbers. Storing the type requires CrateDB 5.9 or later.

Values are written exactly: a Decimal reaches the database with every digit it was given. Reading is narrower, because the driver reads the numbers in a response as floats, so a returned value carries at most the digits a float holds.

Reflection recovers a column’s type from its name alone, so a reflected NUMERIC column carries no precision and cannot be rendered back into DDL.