# SQL **URL:** https://community.cratedb.com/c/cratedb/sql/19.md?page=1 [Latest](https://community.cratedb.com/latest.md) · [Categories](https://community.cratedb.com/categories.md) · [Tags](https://community.cratedb.com/tags.md) **Page:** 2 --- ## [SQL - cast to class java.lang.String error](https://community.cratedb.com/t/sql-cast-to-class-java-lang-string-error/1240)
**Author:** [@Alin\_Mihut](https://community.cratedb.com/u/Alin_Mihut)\ **Replies:** 15\ **Last updated:** [October 19, 2022, 3:01pm UTC](https://community.cratedb.com/t/sql-cast-to-class-java-lang-string-error/1240 "2022-10-19T15:01:44Z")
Hello guys, I encountered the following errors while trying to execute a SELECT query with ORDER BY, LIMIT and OFFSET. And I’m trying to figure out if I’m missing something in my setup configuration. Does anyone have an… --- ## [Date\_bin grouping](https://community.cratedb.com/t/date-bin-grouping/1249)
**Author:** [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\ **Replies:** 2\ **Last updated:** [October 12, 2022, 2:43pm UTC](https://community.cratedb.com/t/date-bin-grouping/1249 "2022-10-12T14:43:29Z")
Hi all, I’ve got a bit of an issue understanding date\_bin, as its not returning what I think it should. I’ve got a data set that is 1 minute apart for each row and I’m doing a specific operation of dealing with one d… --- ## [Selecting data using \_id values with "in" operator](https://community.cratedb.com/t/selecting-data-using-id-values-with-in-operator/1231)
**Author:** [@Bryan\_Ford](https://community.cratedb.com/u/Bryan_Ford)\ **Replies:** 4\ **Last updated:** [September 30, 2022, 12:47pm UTC](https://community.cratedb.com/t/selecting-data-using-id-values-with-in-operator/1231 "2022-09-30T12:47:52Z")
I am curious what I might need to change in order to select rows that match from a subset using the \_id value. What works for me is the simple case where I perform a single comparison: -- Returns a single matching reco… --- ## [Make crash execute a bunch of commands](https://community.cratedb.com/t/make-crash-execute-a-bunch-of-commands/463)
**Author:** [@Jurgen\_Zornig](https://community.cratedb.com/u/Jurgen_Zornig)\ **Replies:** 5\ **Last updated:** [September 14, 2022, 7:44pm UTC](https://community.cratedb.com/t/make-crash-execute-a-bunch-of-commands/463 "2022-09-14T19:44:47Z")
I have a litte init script which consists of 3 commands (CREATE REPOSITORY; RESTORE SNAPSHOT; CREATE VIEW;) and I would like to make my crate cluster provisioned in an Azure Kubernetes Service to run them after the clust… --- ## [Adding column generates error](https://community.cratedb.com/t/adding-column-generates-error/1104)
**Author:** [@wvdamme](https://community.cratedb.com/u/wvdamme)\ **Replies:** 3\ **Last updated:** [May 10, 2022, 11:23am UTC](https://community.cratedb.com/t/adding-column-generates-error/1104 "2022-05-10T11:23:30Z")
The instruction: ALTER TABLE {tablename} ADD {column name} double precision; generates the error: mapper has different length\_limit settings, current \[512\], merged \[null\]. What does this mean? The instruction works in… --- ## [Help with Data Ingestion](https://community.cratedb.com/t/help-with-data-ingestion/1076)
**Author:** [@phomuth](https://community.cratedb.com/u/phomuth)\ **Replies:** 3\ **Last updated:** [April 6, 2022, 3:10pm UTC](https://community.cratedb.com/t/help-with-data-ingestion/1076 "2022-04-06T15:10:06Z")
Currently, I am having issues using the Copy From statement to import data into CrateDB. I have created a table already in CrateDB with the correct data types and naming scheme. Then I attempted the following code: Cop… --- ## [Strange behavior with ILIKE operator](https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070)
**Author:** [@Amod](https://community.cratedb.com/u/Amod)\ **Replies:** 4\ **Last updated:** [April 5, 2022, 6:21pm UTC](https://community.cratedb.com/t/strange-behavior-with-ilike-operator/1070 "2022-04-05T18:21:38Z")
Hi, I am trying to use ILIKE operator for case insensitive search. Here I am facing issue with specific character ‘{’ only when I use ILIKE operator. Same where condition works with LIKE operator. e.g. Following query … --- ## [Cratedb loop through array](https://community.cratedb.com/t/cratedb-loop-through-array/1062)
**Author:** [@arafaraf](https://community.cratedb.com/u/arafaraf)\ **Replies:** 10\ **Last updated:** [March 25, 2022, 2:34pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062 "2022-03-25T14:34:06Z")
Hey guys, how would I iterate through an array in cratedb. So I have values X and Y and I want to check if a value in my array is between X and Y. so my row has an array field as such \[5, 7, 20\] For each row in my tab… --- ## [Tables name convention](https://community.cratedb.com/t/tables-name-convention/994)
**Author:** [@cande1gut](https://community.cratedb.com/u/cande1gut)\ **Replies:** 6\ **Last updated:** [February 9, 2022, 5:47pm UTC](https://community.cratedb.com/t/tables-name-convention/994 "2022-02-09T17:47:31Z")
While trying to define a camel case name convention to my tables, it looks like this is not possible. Is it expected to be that way and only work in a snake case fashion? --- ## [NullPointerException](https://community.cratedb.com/t/nullpointerexception/978)
**Author:** [@paragdodia](https://community.cratedb.com/u/paragdodia)\ **Replies:** 1\ **Last updated:** [January 11, 2022, 1:10pm UTC](https://community.cratedb.com/t/nullpointerexception/978 "2022-01-11T13:10:59Z")
We have a table with 43.6 million records. Partition on the table is on the basis of weeks. Max. records in a single partition is 28,992,722. Table has index on all columns used in where clause. we are also using ‘match’… --- ## [Group by query with join taking forever](https://community.cratedb.com/t/group-by-query-with-join-taking-forever/868)
**Author:** [@kamcio181](https://community.cratedb.com/u/kamcio181)\ **Replies:** 14\ **Last updated:** [December 21, 2021, 10:29am UTC](https://community.cratedb.com/t/group-by-query-with-join-taking-forever/868 "2021-12-21T10:29:03Z")
Hello, during tests on 8 nodes, bare metal CrateDB 4.6.4 cluster we noticed that one groupby query including join is taking forever to finish. Reproduction steps CREATE TABLE IF NOT EXISTS test\_table1 ( "id" BIGINT… --- ## [Rows need significantly more storage as others in the same table and pretty much the same content](https://community.cratedb.com/t/rows-need-significantly-more-storage-as-others-in-the-same-table-and-pretty-much-the-same-content/912)
**Author:** [@Jurgen\_Zornig](https://community.cratedb.com/u/Jurgen_Zornig)\ **Replies:** 10\ **Last updated:** [December 17, 2021, 4:26pm UTC](https://community.cratedb.com/t/rows-need-significantly-more-storage-as-others-in-the-same-table-and-pretty-much-the-same-content/912 "2021-12-17T16:26:53Z")
I already mentioned this in another Topic, then thougth it is solved, but still the issue is emminent and I tried to better circumscribe on this. I have a table with the following scheme: CREATE TABLE doc.odp\_ts ( … --- ## [Can I create a function in CRATEDB that select \* from table](https://community.cratedb.com/t/can-i-create-a-function-in-cratedb-that-select-from-table/930)
**Author:** [@greg\_lovelace](https://community.cratedb.com/u/greg_lovelace)\ **Replies:** 1\ **Last updated:** [December 7, 2021, 6:54pm UTC](https://community.cratedb.com/t/can-i-create-a-function-in-cratedb-that-select-from-table/930 "2021-12-07T18:54:12Z")
I tried creating this FUNCTION and get the error; SQL Error \[42601\]: Unterminated dollar quote started at position 0 in SQL $BODY$. Expected terminating $$ Has anyone tried creating function that acts like a stored pro… --- ## [PostgreSQL time-related queries faster than the same queries in CrateDB](https://community.cratedb.com/t/postgresql-time-related-queries-faster-than-the-same-queries-in-cratedb/921)
**Author:** [@pavol.pluta](https://community.cratedb.com/u/pavol.pluta)\ **Replies:** 1\ **Last updated:** [December 1, 2021, 9:34pm UTC](https://community.cratedb.com/t/postgresql-time-related-queries-faster-than-the-same-queries-in-cratedb/921 "2021-12-01T21:34:06Z")
Hello, We are considering to migrate to CrateDB from Postgres as data that we are using are from real-time sensors with timestamps and geospatial information. Crate seems very promising as, according to your homepage, i… --- ## [Get list of names/sql code for analizers](https://community.cratedb.com/t/get-list-of-names-sql-code-for-analizers/900)
**Author:** [@Valeriy\_Dzhura](https://community.cratedb.com/u/Valeriy_Dzhura)\ **Replies:** 5\ **Last updated:** [November 22, 2021, 6:26pm UTC](https://community.cratedb.com/t/get-list-of-names-sql-code-for-analizers/900 "2021-11-22T18:26:13Z")
Hello guys, Could someone show me how I can get list of all custom analysers (list of names, sql code) that was created like this example: CREATE ANALYZER myanalyzer ( TOKENIZER whitespace, TOKEN\_FILTERS ( lowe… --- ## [Fuzzy Search & Synonyms](https://community.cratedb.com/t/fuzzy-search-synonyms/889)
**Author:** [@Todd\_Bryant](https://community.cratedb.com/u/Todd_Bryant)\ **Replies:** 3\ **Last updated:** [November 19, 2021, 2:10pm UTC](https://community.cratedb.com/t/fuzzy-search-synonyms/889 "2021-11-19T14:10:48Z")
I have a table with firstname and lastname. I want to find matches that tolerate minor inversions such as where lastname = ‘bronw’ (actual brown). Also, I would like to use a synonym file to fine nick names and diminu… --- ## [SELECT with Join takes forever](https://community.cratedb.com/t/select-with-join-takes-forever/888)
**Author:** [@fox\_pluto](https://community.cratedb.com/u/fox_pluto)\ **Replies:** 4\ **Last updated:** [November 15, 2021, 4:39pm UTC](https://community.cratedb.com/t/select-with-join-takes-forever/888 "2021-11-15T16:39:13Z")
Hi, I have just started using CrateDB and I have created two table, the first one with 3,3 Millions of records (Events table) and the second one with 60k records (Stats table). These are the tables: create table event… --- ## [Why does COPY TO create empty JSON files and how is data distributed to these files?](https://community.cratedb.com/t/why-does-copy-to-create-empty-json-files-and-how-is-data-distributed-to-these-files/887)
**Author:** [@Emre\_Sevinc](https://community.cratedb.com/u/Emre_Sevinc)\ **Replies:** 1\ **Last updated:** [November 12, 2021, 3:04pm UTC](https://community.cratedb.com/t/why-does-copy-to-create-empty-json-files-and-how-is-data-distributed-to-these-files/887 "2021-11-12T15:04:32Z")
Hello, Coming from PostgreSQL background, I’m a bit confused about exporting data out of CrateDB using COPY TO command. I’m following the instructions at https://crate.io/docs/crate/reference/en/4.6/sql/statements/copy-… --- ## [Create New database in the same cluster](https://community.cratedb.com/t/create-new-database-in-the-same-cluster/885)
**Author:** [@Savvas\_Savva](https://community.cratedb.com/u/Savvas_Savva)\ **Replies:** 2\ **Last updated:** [November 11, 2021, 3:05pm UTC](https://community.cratedb.com/t/create-new-database-in-the-same-cluster/885 "2021-11-11T15:05:47Z")
Hi, I am new in crateDB. I wonder if there is any way to create another Database except from the Doc and Blob ones. Must I create a new user and a new cluster for it ? is there any SQL Command to create new Database? --- ## [LAST\_VALUE() ignores Window partition](https://community.cratedb.com/t/last-value-ignores-window-partition/869)
**Author:** [@Jurgen\_Zornig](https://community.cratedb.com/u/Jurgen_Zornig)\ **Replies:** 4\ **Last updated:** [October 27, 2021, 2:18pm UTC](https://community.cratedb.com/t/last-value-ignores-window-partition/869 "2021-10-27T14:18:06Z")
I have a table of Ids which are versioned by timestamp (its just a DataVault satellite table) and I´d like to utilize window functions to get the most recent version of each Id like so: SELECT a.\* FROM odp.s\_ent… --- ## [SQL Freeze after sync done](https://community.cratedb.com/t/sql-freeze-after-sync-done/860)
**Author:** [@ilbee](https://community.cratedb.com/u/ilbee)\ **Replies:** 3\ **Last updated:** [October 20, 2021, 6:54am UTC](https://community.cratedb.com/t/sql-freeze-after-sync-done/860 "2021-10-20T06:54:03Z")
Hello, We have a problem with crateDB in production environment. We have 3 nodes in the cluster on 3 different servers. All 3 nodes are eligible to become master. Each server is running on linux (redhat 8.3) and has 6… --- ## [Optimal way to do SELECT without saturating the heap](https://community.cratedb.com/t/optimal-way-to-do-select-without-saturating-the-heap/855)
**Author:** [@cande1gut](https://community.cratedb.com/u/cande1gut)\ **Replies:** 6\ **Last updated:** [October 15, 2021, 6:51am UTC](https://community.cratedb.com/t/optimal-way-to-do-select-without-saturating-the-heap/855 "2021-10-15T06:51:36Z")
What would be a good practice to request big amounts of data from crate. I have a use case where I need to fetch all data available in a table and I wonder if there is a way to request all the data without saturating the… --- ## [COPY FROM csv fails successfully](https://community.cratedb.com/t/copy-from-csv-fails-successfully/829)
**Author:** [@inviridi](https://community.cratedb.com/u/inviridi)\ **Replies:** 7\ **Last updated:** [October 4, 2021, 11:17am UTC](https://community.cratedb.com/t/copy-from-csv-fails-successfully/829 "2021-10-04T11:17:35Z")
Hello everyone, I try to import a csv file into CrateDB like this: COPY imp5 FROM 'file:///opt/MIB/test.csv'; and get the result: COPY OK, 0 records affected (1.437 seconds) There is apparently no issue with the Cra… --- ## [Querying whole OBJECT or Document using LIKE condition](https://community.cratedb.com/t/querying-whole-object-or-document-using-like-condition/837)
**Author:** [@chandan-digumarthy](https://community.cratedb.com/u/chandan-digumarthy)\ **Replies:** 0\ **Last updated:** [October 1, 2021, 8:10am UTC](https://community.cratedb.com/t/querying-whole-object-or-document-using-like-condition/837 "2021-10-01T08:10:36Z")
Hello, I have a table with one of the field defined as "attributes" OBJECT (DYNAMIC). Now, my usecase is to be able to check if any particular string is part of this OBJECT. In SQL terms I want to execute a like comman… --- ## [Create new schema](https://community.cratedb.com/t/create-new-schema/828)
**Author:** [@Raghavendra\_Hegde](https://community.cratedb.com/u/Raghavendra_Hegde)\ **Replies:** 1\ **Last updated:** [September 27, 2021, 8:28pm UTC](https://community.cratedb.com/t/create-new-schema/828 "2021-09-27T20:28:34Z")
how to create new schema on CrateDB? I tried with below command and got error. CREATE SCHEMA test2 AUTHORIZATION testuser; Error! SQLParseException\[line 1:8: no viable alternative at input ‘CREATE SCHEMA’\] --- ## [Insert an array of json objects ERROR](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819)
**Author:** [@Sven](https://community.cratedb.com/u/Sven)\ **Replies:** 6\ **Last updated:** [September 23, 2021, 11:52am UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819 "2021-09-23T11:52:04Z")
Hi community, I am quite new and cant make sense of the documentation. I want to insert an array of JSON objects but I get ERROR Cannot cast expressions from type text to type object\_array insert into prices.latest\_pr… --- ## [User Defined Aggregation Function](https://community.cratedb.com/t/user-defined-aggregation-function/810)
**Author:** [@Francisco\_Javier\_Par](https://community.cratedb.com/u/Francisco_Javier_Par)\ **Replies:** 3\ **Last updated:** [September 17, 2021, 2:11pm UTC](https://community.cratedb.com/t/user-defined-aggregation-function/810 "2021-09-17T14:11:18Z")
Hello, I have found in the documentation a description to create a custom user defined function (UDF). However, I could not find any reference to create User Defined Aggregate Function (UDAF), is this possible? Where ca… --- ## [Select value from object(dynamic) within object(dynamic)](https://community.cratedb.com/t/select-value-from-object-dynamic-within-object-dynamic/775)
**Author:** [@rafluq](https://community.cratedb.com/u/rafluq)\ **Replies:** 2\ **Last updated:** [August 12, 2021, 7:23am UTC](https://community.cratedb.com/t/select-value-from-object-dynamic-within-object-dynamic/775 "2021-08-12T07:23:25Z")
Hello! I have the following table (obtained through the “SHOW CREATE TABLE” SQL directive): "doc"."etgeometry\_msgs%2ftwist" ( "entity\_id" TEXT, "entity\_type" TEXT, "time\_index" TIMESTAMP WITH TIME ZONE, "fiw… --- ## [Timestamp without time zone in local time](https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765)
**Author:** [@inviridi](https://community.cratedb.com/u/inviridi)\ **Replies:** 4\ **Last updated:** [August 4, 2021, 9:45pm UTC](https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765 "2021-08-04T21:45:45Z")
Howdy folks, I am logging machine data and would like to have a time stamp with local time from the machine. The (generated) column is created as ts\_crate TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS CURRENT\_TIMESTA… --- ## [CrateDB | Performance Optimisation in Left Joins](https://community.cratedb.com/t/cratedb-performance-optimisation-in-left-joins/748)
**Author:** [@sambhav](https://community.cratedb.com/u/sambhav)\ **Replies:** 1\ **Last updated:** [July 20, 2021, 7:05pm UTC](https://community.cratedb.com/t/cratedb-performance-optimisation-in-left-joins/748 "2021-07-20T19:05:27Z")
Crate Version: 4.3.2 We have 2 tables Table\_A and Table\_B. Table\_A consists of 40k records and Table\_B consists of 400k records. Whenever we apply left join the crate DB kind of hangs and results coming in 140 secs… tha… [Previous page](https://community.cratedb.com/c/cratedb/sql/19.md) [Next page](https://community.cratedb.com/c/cratedb/sql/19.md?page=2)