--- title: Real-Time Data Classification with Generated Column description: 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... canonical: https://daily.dev/posts/real-time-data-classification-with-generated-column-wh0q0cd09 twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Real-Time Data Classification with Generated Column | daily.dev og:description: 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... og:url: https://daily.dev/posts/real-time-data-classification-with-generated-column-wh0q0cd09 og:image: https://api.daily.dev/og/posts/wh0Q0CD09.png og:image:alt: Real-Time Data Classification with Generated Column og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Real-Time Data Classification with Generated Column **[CrateDB](https://daily.dev/sources/cratedb)** · 6 min read · 0 upvotes · 0 comments ## Summary 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. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## 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._ ## Similar posts on daily.dev - [What Is a Columnar Database? Understanding the Power Behind Real-Time Analytics](https://daily.dev/posts/what-is-a-columnar-database-understanding-the-power-behind-real-time-analytics-ddxxul0dj) · CrateDB · 0 upvotes · 0 comments - [How to Add a New Sensor Type to Your Industrial Database Without Pipeline Downtime](https://daily.dev/posts/how-to-add-a-new-sensor-type-to-your-industrial-database-without-pipeline-downtime-ao77ujcc3) · CrateDB · 0 upvotes · 0 comments --- Tags: [#sql](https://daily.dev/tags/sql), [#cratedb](https://daily.dev/tags/cratedb) [View this post on daily.dev](https://daily.dev/posts/real-time-data-classification-with-generated-column-wh0q0cd09) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Real-Time Data Classification with Generated Column","url":"https://daily.dev/posts/real-time-data-classification-with-generated-column-wh0q0cd09","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/real-time-data-classification-with-generated-column-wh0q0cd09"},"datePublished":"2026-08-24T14:56:50.247Z","dateModified":"2026-08-24T14:57:13.037Z","description":"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...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/7ca4ca18be3e04763cbaa6cfacd69e3d?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/7ca4ca18be3e04763cbaa6cfacd69e3d?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"CrateDB","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"CrateDB","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/46d00ffb81d0452e90976cc491162d6e","url":"https://daily.dev/sources/cratedb"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/real-time-data-classification-with-generated-column-wh0q0cd09","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"sql,cratedb","timeRequired":"PT6M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"CrateDB","item":"https://daily.dev/sources/cratedb"},{"@type":"ListItem","position":3,"name":"Real-Time Data Classification with Generated Column"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/real-time-data-classification-with-generated-column-wh0q0cd09#faq","mainEntity":[{"@type":"Question","name":"How can I flag oversize bags or records automatically as they are inserted into a database instead of computing it at query time?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How do I automatically partition a time-series table by week without the application knowing about partitions?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How do I convert raw latitude and longitude fields into a usable geo point for mapping and geo queries in CrateDB?","acceptedAnswer":{"@type":"Answer","text":"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."}}]} ```