Support for CREATE TYPE

[Alibirb]

CrateDB version

5.6.4

CrateDB setup information

No response

Problem description

I'm trying to replace postgres with CrateDB, but it seems CrateDB doesn't support creating types like postgres does, and our existing tables rely on a custom type.

Steps to Reproduce

CREATE TYPE sort_key AS (
    time timestamptz,
    ident text
);

Actual Result

no viable alternative at input 'CREATE TYPE'

Expected Result

Should create the type

[seut]

Wouldn't using an object typed column defined at the table definition a working alternate?

[Alibirb]

Wouldn't using an object typed column defined at the table definition a working alternate?

Maybe. I don't have much SQL knowledge so I don't know the implications.

But upon further investigation, it seems like this is far from the only postgres feature we use that CrateDB is missing. Docs say user defined functions are only supported in JavaScript (we define them in SQL), no support for creating rules, or triggers, or aggregates... I suspect these may be a bit harder to work around.

But given that the lack of support for CREATE TYPE isn't mentioned in the docs under "unsupported features and functions", I think this support should either be added, or the docs should be updated to acknowledge that it's missing.

[surister]

CrateDB supports many aggregate functions, https://cratedb.com/docs/crate/reference/en/5.6/general/builtins/aggregation.html#aggregation Is there any that you are missing from Crate?

[Alibirb]

CrateDB supports many aggregate functions, https://cratedb.com/docs/crate/reference/en/5.6/general/builtins/aggregation.html#aggregation Is there any that you are missing from Crate?

I'm missing the ability to define my own aggregation functions.

[hlcianfagna]

I'm missing the ability to define my own aggregation functions.

Hi, it is a workaround, but you may want to take a look at how this is achieved with an UDF in https://community.cratedb.com/t/advanced-downsampling-with-the-lttb-algorithm/1287