CrateDB's generated columns let you classify and index streaming data at insert time rather than in a separate cleanup pass. A worked example models baggage-handling events at Heathrow Terminal 5 using five generated columns: an oversize boolean flag from a CASE expression, a reported_late flag comparing event timestamps to catch upstream lag, a geo_location field converting raw lat/long into a GEO_POINT, an in_t5 boolean from a within() polygon check, and an event_week timestamp used to auto-partition the table by week. Because these values are computed once on write and stored/indexed like any other column, later queries become cheap boolean filters instead of repeated computations, and old data can be dropped by removing entire weekly partitions.
Questions this post answers
How can I flag oversize bags or records automatically as they are inserted into a database instead of computing it at query time?
Use a generated column with a CASE expression that evaluates the business rule on every insert. For example, a CrateDB BOOLEAN column defined as GENERATED ALWAYS AS a CASE statement over length, width, and height columns computes an oversize flag automatically, so any application inserting rows gets the same consistent result without repeating logic in every query. Explore how developers apply generated columns for consistent data rules on daily.dev.
How do I automatically partition a time-series table by week without the application knowing about partitions?
Create a generated column that truncates the event timestamp to the start of its week using date_trunc('week', conveyer_timestamp), and partition the table by that column. Rows then route themselves into weekly partitions automatically, and dropping data older than a retention window becomes a matter of dropping whole partitions instead of deleting millions of rows individually. Developers designing time-series retention strategies can track database techniques like this on daily.dev.
How do I convert raw latitude and longitude fields into a usable geo point for mapping and geo queries in CrateDB?
Define a generated column typed as GEO_POINT that is computed from the raw lat and long fields, for example GENERATED ALWAYS AS [reported_location['long'], reported_location['lat']]. This produces a proper GEO_POINT value stored and indexed on every insert, enabling CrateDB's geo functions and direct plotting on tools like Grafana without any query-time conversion. Anyone building geospatial dashboards can follow database and mapping patterns like this on daily.dev.