# \#sql **URL:** https://community.cratedb.com/tag/sql/3.md [Latest](https://community.cratedb.com/latest.md) · [Categories](https://community.cratedb.com/categories.md) · [Tags](https://community.cratedb.com/tags.md) --- ## [The LIKE/ILIKE keyword in CrateDB does not work when the target string contains newline (\\n) or tab (\\t) characters. This causes pattern matching to fail for multi-line or formatted text values](https://community.cratedb.com/t/the-like-ilike-keyword-in-cratedb-does-not-work-when-the-target-string-contains-newline-n-or-tab-t-characters-this-causes-pattern-matching-to-fail-for-multi-line-or-formatted-text-values/2089)
**Author:** [@anujjaiswar](https://community.cratedb.com/u/anujjaiswar)\ **Replies:** 3\ **Last updated:** [January 8, 2026, 6:39am UTC](https://community.cratedb.com/t/the-like-ilike-keyword-in-cratedb-does-not-work-when-the-target-string-contains-newline-n-or-tab-t-characters-this-causes-pattern-matching-to-fail-for-multi-line-or-formatted-text-values/2089 "2026-01-08T06:39:04Z")
For example, the query below returns an empty result: SELECT \* FROM table WHERE message ILIKE ‘%A user account was%’; But the actual message column value is: A user account was deleted accountid : amit --- ## [Circuit Breaker Error on COLLECT\_SET Aggregation - "breaker would use 18gb in total. Limit is 18gb"](https://community.cratedb.com/t/circuit-breaker-error-on-collect-set-aggregation-breaker-would-use-18gb-in-total-limit-is-18gb/2080)
**Author:** [@varad\_Deshmukh](https://community.cratedb.com/u/varad_Deshmukh)\ **Replies:** 1\ **Last updated:** [December 5, 2025, 11:07am UTC](https://community.cratedb.com/t/circuit-breaker-error-on-collect-set-aggregation-breaker-would-use-18gb-in-total-limit-is-18gb/2080 "2025-12-05T11:07:33Z")
Hello CrateDB Community, I’m experiencing circuit breaker errors during aggregation queries (specifically collect operations) and would appreciate guidance on optimization strategies. Environment: Crate DB cluster:… --- ## [How to access nested object keys](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070)
**Author:** [@volker](https://community.cratedb.com/u/volker)\ **Replies:** 7\ **Last updated:** [August 19, 2025, 3:47pm UTC](https://community.cratedb.com/t/how-to-access-nested-object-keys/2070 "2025-08-19T15:47:23Z")
Hi everyone, I’m working with CrateDB 5.10.11 and I’m having trouble accessing nested object keys in my queries. I keep getting this error: UnsupportedFeatureException\[Can’t handle Symbol \[SimpleReference: fields\[‘aen… --- ## [Created\_at not populated with default value during insert in CrateDB via C#](https://community.cratedb.com/t/created-at-not-populated-with-default-value-during-insert-in-cratedb-via-c/2064)
**Author:** [@Ayas\_Rasheed](https://community.cratedb.com/u/Ayas_Rasheed)\ **Replies:** 1\ **Last updated:** [August 14, 2025, 8:25am UTC](https://community.cratedb.com/t/created-at-not-populated-with-default-value-during-insert-in-cratedb-via-c/2064 "2025-08-14T08:25:50Z")
CREATE TABLE IF NOT EXISTS issue ( object\_id TEXT, -- stores Guid as string element\_type\_id TEXT, tag\_name TEXT, tag\_ts TIMESTAMP, -- from TagTs ingest\_ts TIMESTAM… --- ## [How to perform a fuzzy search on the elements of a text array](https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060)
**Author:** [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\ **Replies:** 4\ **Last updated:** [August 11, 2025, 1:25pm UTC](https://community.cratedb.com/t/how-to-perform-a-fuzzy-search-on-the-elements-of-a-text-array/2060 "2025-08-11T13:25:17Z")
Hi team, I’m using CrateDB 5.10.11, now I have a table to store application log in object type, and some column is ARRAY(TEXT), I want to perform fuzzy query on these column with ARRAY(TEXT). How to implement it? Thanks. … --- ## [Kafka connect failed sink data to cratedb while the data doesn't exist in record](https://community.cratedb.com/t/kafka-connect-failed-sink-data-to-cratedb-while-the-data-doesnt-exist-in-record/2058)
**Author:** [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\ **Replies:** 5\ **Last updated:** [August 4, 2025, 10:53am UTC](https://community.cratedb.com/t/kafka-connect-failed-sink-data-to-cratedb-while-the-data-doesnt-exist-in-record/2058 "2025-08-04T10:53:03Z")
We’re using CrateDB 5.10.11, and using kafka connect sink data to it, after running for a while the connect encounter below error. But the record don’t contain the value 08:11:26.000Z, so I confused, how to locate the r… --- ## [Min() and max() functions return null values even if the field contains data](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036)
**Author:** [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\ **Replies:** 6\ **Last updated:** [June 13, 2025, 1:18pm UTC](https://community.cratedb.com/t/min-and-max-functions-return-null-values-even-if-the-field-contains-data/2036 "2025-06-13T13:18:27Z")
Hi Team, we have a table metrics that store prometheus data in cratedb 5.10.7, while both the min() and max() functions return null values, even if the field day\_\_generated itself contains data. cr\> select version(); +-… --- ## [Does CrateDB Support Materialized Views? Any Workarounds?](https://community.cratedb.com/t/does-cratedb-support-materialized-views-any-workarounds/2002)
**Author:** [@Dennis\_Jose](https://community.cratedb.com/u/Dennis_Jose)\ **Replies:** 5\ **Last updated:** [May 26, 2025, 11:54am UTC](https://community.cratedb.com/t/does-cratedb-support-materialized-views-any-workarounds/2002 "2025-05-26T11:54:55Z")
I’m exploring CrateDB and found that it supports downsampling using DATE\_BIN and the LTTB algorithm. However, I couldn’t find direct support for materialized views where the results are stored in a table for efficient qu… --- ## [Create read-only database user by using "GRANT DQL"](https://community.cratedb.com/t/create-read-only-database-user-by-using-grant-dql/2031)
**Author:** [@amotl](https://community.cratedb.com/u/amotl)\ **Replies:** 0\ **Last updated:** [May 17, 2025, 6:45pm UTC](https://community.cratedb.com/t/create-read-only-database-user-by-using-grant-dql/2031 "2025-05-17T18:45:57Z")
Introduction Data flows in analytical applications like pulling data into business intelligence or visualization tools typically only needs read-only access to database tables and views. CrateDB’s privilege system pro… --- ## [Poor Performance: Snapshot creation takes over an hour for a 5-row table](https://community.cratedb.com/t/poor-performance-snapshot-creation-takes-over-an-hour-for-a-5-row-table/2017)
**Author:** [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\ **Replies:** 0\ **Last updated:** [March 27, 2025, 8:17am UTC](https://community.cratedb.com/t/poor-performance-snapshot-creation-takes-over-an-hour-for-a-5-row-table/2017 "2025-03-27T08:17:04Z")
In CrateDB 5.5.2, snapshot creation for a 5-row table takes over an hour. What might cause this? cr\> select count(\*) from xxaps.cs\_test; +----------+ | count(\*) | +----------+ | 5 | +----------+ SELECT 1 row in s… --- ## [Couldn't decrease number of shards for table](https://community.cratedb.com/t/couldnt-decrease-number-of-shards-for-table/2014)
**Author:** [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\ **Replies:** 2\ **Last updated:** [March 26, 2025, 8:33am UTC](https://community.cratedb.com/t/couldnt-decrease-number-of-shards-for-table/2014 "2025-03-26T08:33:46Z")
I’m using CrateDB 5.10.3 and I want decrease the number of shards for some tables, while getting below error. cr\> SET GLOBAL PERSISTENT "cluster.max\_shards\_per\_node" = '1500'; SET OK, 1 row affected (0.078 sec) cr\> ALTE… --- ## [Prepared Statement Query Returns Duplicate Records but CrateDB Console Query Doesn’t](https://community.cratedb.com/t/prepared-statement-query-returns-duplicate-records-but-cratedb-console-query-doesn-t/1966)
**Author:** [@Sunil](https://community.cratedb.com/u/Sunil)\ **Replies:** 1\ **Last updated:** [January 23, 2025, 2:43pm UTC](https://community.cratedb.com/t/prepared-statement-query-returns-duplicate-records-but-cratedb-console-query-doesn-t/1966 "2025-01-23T14:43:08Z")
Hello, When we execute a query using a prepared statement, it returns duplicate records, while running the same query directly on the CrateDB console gives the correct results. This issue can be reproduced using this d… --- ## [Resampling time-series data with DATE\_BIN](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009)
**Author:** [@hammerhead](https://community.cratedb.com/u/hammerhead)\ **Replies:** 5\ **Last updated:** [October 15, 2024, 9:28am UTC](https://community.cratedb.com/t/resampling-time-series-data-with-date-bin/1009 "2024-10-15T09:28:18Z")
Introduction CrateDB 4.7 adds a new DATE\_BIN function, offering greater flexibility for grouping rows into time buckets. This article will show how to use DATE\_BIN to group rows into time buckets and resample the value… --- ## [How to import csv file which contains into table which have json columns](https://community.cratedb.com/t/how-to-import-csv-file-which-contains-into-table-which-have-json-columns/1870)
**Author:** [@sushilr007](https://community.cratedb.com/u/sushilr007)\ **Replies:** 0\ **Last updated:** [October 1, 2024, 11:28am UTC](https://community.cratedb.com/t/how-to-import-csv-file-which-contains-into-table-which-have-json-columns/1870 "2024-10-01T11:28:33Z")
I am trying to import csv data in table using below command COPY table1(col1,col2,col3) from 'file:///home/tab.csv' WITH (format= 'csv' , delimiter = ',') RETURN SUMMARY; I am not getting any kind of error but in col3 i… --- ## [How do we access words' frequencies?](https://community.cratedb.com/t/how-do-we-access-words-frequencies/1865)
**Author:** [@ibrhmkoz](https://community.cratedb.com/u/ibrhmkoz)\ **Replies:** 0\ **Last updated:** [September 30, 2024, 12:16pm UTC](https://community.cratedb.com/t/how-do-we-access-words-frequencies/1865 "2024-09-30T12:16:34Z")
I have an inverted index over certain columns of a table as follows: index keyword\_ft using fulltext(title, content, transcript\['text'\]) with (analyzer = 'english'), Can I access this inverted index to learn how many … --- ## [Failed to update from joined table](https://community.cratedb.com/t/failed-to-update-from-joined-table/1845)
**Author:** [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\ **Replies:** 1\ **Last updated:** [September 11, 2024, 9:11am UTC](https://community.cratedb.com/t/failed-to-update-from-joined-table/1845 "2024-09-11T09:11:57Z")
Hi Team, I’m using CrateDB 5.5.2 and tried to update a field in a table with values obtained from joining another table. However, I encountered the following error, but I don’t know the exact cause. UPDATE Ebs.Rwmv\_Xxmt… --- ## [Drop user or drop schema?](https://community.cratedb.com/t/drop-user-or-drop-schema/1819)
**Author:** [@awan1](https://community.cratedb.com/u/awan1)\ **Replies:** 5\ **Last updated:** [July 25, 2024, 2:42pm UTC](https://community.cratedb.com/t/drop-user-or-drop-schema/1819 "2024-07-25T14:42:47Z")
is there an easy way to drop a schema or user in CrateDB? I was not able to find anything in the documentation about it. --- ## [Query partition size from table](https://community.cratedb.com/t/query-partition-size-from-table/1785)
**Author:** [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\ **Replies:** 2\ **Last updated:** [June 13, 2024, 3:31pm UTC](https://community.cratedb.com/t/query-partition-size-from-table/1785 "2024-06-13T15:31:40Z")
Hi Community Is there a way to get the size of a partition within a table? I would like to start a new partition when the size exceed a certain amount of space. Regards --- ## [Sharding and partitioning guide for time-series data](https://community.cratedb.com/t/sharding-and-partitioning-guide-for-time-series-data/737)
**Author:** [@jayeff](https://community.cratedb.com/u/jayeff)\ **Replies:** 0\ **Last updated:** [July 2, 2021, 9:07am UTC](https://community.cratedb.com/t/sharding-and-partitioning-guide-for-time-series-data/737 "2021-07-02T09:07:46Z")
The goal of this guide is to support you with building a sharding and partitioning strategy for your time series data. Let’s start with your time series data which you want to store in a table in CrateDB. You need to c… --- ## [Cluster does not respond after synchronization is complete](https://community.cratedb.com/t/cluster-does-not-respond-after-synchronization-is-complete/1747)
**Author:** [@ilbee](https://community.cratedb.com/u/ilbee)\ **Replies:** 1\ **Last updated:** [April 3, 2024, 8:11am UTC](https://community.cratedb.com/t/cluster-does-not-respond-after-synchronization-is-complete/1747 "2024-04-03T08:11:37Z")
Hello, I allow myself to dig up my ticket: SQL Freeze after sync done After several version upgrades (4.4 to 4.6) the problem is still present. As long as the synchronization does not reach 100%, the 3 nodes remain ac… --- ## [Alter table and add new column if already exists](https://community.cratedb.com/t/alter-table-and-add-new-column-if-already-exists/1748)
**Author:** [@Alin\_Mihut](https://community.cratedb.com/u/Alin_Mihut)\ **Replies:** 1\ **Last updated:** [April 3, 2024, 8:04am UTC](https://community.cratedb.com/t/alter-table-and-add-new-column-if-already-exists/1748 "2024-04-03T08:04:29Z")
Hello, It seems that the IF NOT EXISTS condition is not supported in the ALTER TABLE statement Is there an alternative to execute such statement as below? ALTER TABLE table\_name ADD COLUMN IF NOT EXISTS month AS date\_… --- ## [Using common table expressions to speed up queries](https://community.cratedb.com/t/using-common-table-expressions-to-speed-up-queries/1719)
**Author:** [@hernanc](https://community.cratedb.com/u/hernanc)\ **Replies:** 0\ **Last updated:** [February 22, 2024, 9:25am UTC](https://community.cratedb.com/t/using-common-table-expressions-to-speed-up-queries/1719 "2024-02-22T09:25:57Z")
Today I want to share with you a pattern you can use to replace JOINs with CTEs in your SQL queries and achieve consistent and faster execution times. Consider a database where we store information about invoices, a sim… --- ## [LTTB downsampling to work around Grafana SQL limits](https://community.cratedb.com/t/lttb-downsampling-to-work-around-grafana-sql-limits/1596)
**Author:** [@CArsten](https://community.cratedb.com/u/CArsten)\ **Replies:** 10\ **Last updated:** [February 27, 2024, 11:06am UTC](https://community.cratedb.com/t/lttb-downsampling-to-work-around-grafana-sql-limits/1596 "2024-02-27T11:06:42Z")
Hi, still trying to get me feet wet with CrateDB as a Prometheus long term storage back-end. As I was running into timeout problems when querying via Prometheus I switched to “Grafana SQL” to interact directly with Crat… --- ## [Retrieving records in bulk with a list of primary key values](https://community.cratedb.com/t/retrieving-records-in-bulk-with-a-list-of-primary-key-values/1721)
**Author:** [@hernanc](https://community.cratedb.com/u/hernanc)\ **Replies:** 0\ **Last updated:** [February 23, 2024, 9:30am UTC](https://community.cratedb.com/t/retrieving-records-in-bulk-with-a-list-of-primary-key-values/1721 "2024-02-23T09:30:07Z")
When we send SQL statements to CrateDB they need to be parsed, but in most situations we do not think about this because the resources used for parsing the statements are trivial in relation to what is required to actual… --- ## [Importing and exporting data in CrateDB](https://community.cratedb.com/t/importing-and-exporting-data-in-cratedb/1189)
**Author:** [@rafaelasantana](https://community.cratedb.com/u/rafaelasantana)\ **Replies:** 0\ **Last updated:** [August 8, 2022, 12:38pm UTC](https://community.cratedb.com/t/importing-and-exporting-data-in-cratedb/1189 "2022-08-08T12:38:43Z")
This tutorial is also available in video at CrateDB Video | Fundamentals: Importing and Exporting Data in CrateDB This tutorial presents the basics of COPY FROM and COPY TO in CrateDB. For in-depth details of CrateDB C… --- ## [Objects in CrateDB](https://community.cratedb.com/t/objects-in-cratedb/1188)
**Author:** [@rafaelasantana](https://community.cratedb.com/u/rafaelasantana)\ **Replies:** 0\ **Last updated:** [August 8, 2022, 11:59am UTC](https://community.cratedb.com/t/objects-in-cratedb/1188 "2022-08-08T11:59:06Z")
In this tutorial, you will learn the basics of CrateDB Objects. This tutorial is also available in the video format: CrateDB Video | Fundamentals: Getting Started with CrateDB Objects First, I present a simple use cas… --- ## [Poor performance with five tables using join](https://community.cratedb.com/t/poor-performance-with-five-tables-using-join/1656)
**Author:** [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\ **Replies:** 7\ **Last updated:** [December 19, 2023, 4:17pm UTC](https://community.cratedb.com/t/poor-performance-with-five-tables-using-join/1656 "2023-12-19T16:17:49Z")
We’re using CrateDB 5.5 with one node, and we’re testing a below sql, while it takes about than 75 seconds. Did I configure something wrong？Any suggestion will be appreciated. Server info: //CPU processor : 79 mo… --- ## [Error occurs while using COPY FROM json file when sequence of the records in a file mismatch](https://community.cratedb.com/t/error-occurs-while-using-copy-from-json-file-when-sequence-of-the-records-in-a-file-mismatch/1668)
**Author:** [@sayalithange14](https://community.cratedb.com/u/sayalithange14)\ **Replies:** 4\ **Last updated:** [December 13, 2023, 6:28am UTC](https://community.cratedb.com/t/error-occurs-while-using-copy-from-json-file-when-sequence-of-the-records-in-a-file-mismatch/1668 "2023-12-13T06:28:39Z")
Version : crate-5.5.0 I am using below command to restore json backup in my table - copy temp.my\_table FROM '/tmp/crate-bkp/new/my\_table\_0\_04732d9p60sj8e9o60o30c1g.json' RETURN SUMMARY; It returns with the error - “C… --- ## [Searching DB with where clause on column whose data type is Array](https://community.cratedb.com/t/searching-db-with-where-clause-on-column-whose-data-type-is-array/1622)
**Author:** [@shubham](https://community.cratedb.com/u/shubham)\ **Replies:** 5\ **Last updated:** [October 17, 2023, 3:19pm UTC](https://community.cratedb.com/t/searching-db-with-where-clause-on-column-whose-data-type-is-array/1622 "2023-10-17T15:19:18Z")
Is there any better way to search on the table, if you want to search on the basis of a column whose type is Array. WITH tag\_sums AS ( SELECT value, SUM(amount) AS total\_amount FROM ( SEL… --- ## [View with column swapping](https://community.cratedb.com/t/view-with-column-swapping/1528)
**Author:** [@arturohu](https://community.cratedb.com/u/arturohu)\ **Replies:** 2\ **Last updated:** [June 29, 2023, 7:06am UTC](https://community.cratedb.com/t/view-with-column-swapping/1528 "2023-06-29T07:06:55Z")
Hello, We are currently trying to work with two tables as one using a view. We are experiencing weird behavior in our single-node 4.5.5 CrateDB deployment. We have a main table and a secondary table in order to write … [Next page](https://community.cratedb.com/tag/sql/3.md?match_all_tags=true&page=1&tags%5B%5D=sql)