Specify column type with bulk insert

CrateDB SQL
1

Hi there!

By default object column policy is DYNAMIC. However I need to insert objects with IGNORED column policy into a table with column_policy = ‘dynamic’ so it won’t change the schema and index all object’s fields.

Let’s say I have a table:

CREATE TABLE event (id BIGINT)
WITH (column_policy = 'dynamic')

And I want to insert an event like:

INSERT INTO event (id, ts, obj)
VALUES (1, 1667842489642, '{"prop": "value"}'::OBJECT(IGNORED))

It works great when I use plain SQL. But I didn’t find a way to do it using HTTP endpoint with parameter substitution (for bulk inserts). I am only able to insert it like this:

{
  "stmt": "INSERT INTO event (id, ts, obj) values(?, ?, ?)",
  "bulk_args": [
    [1, 1667842489642, {"prop": "value"}]
  ]
}

But it insets obj column as DYNAMIC object and indexes it.

Is there any way to specify object policy using parameter substitution over HTTP endpoint?
Is there any way to change default column policy of the object?

2

I am a bit confused …

INSERT INTO event (id, ts, obj)
VALUES (1, 1667842489642, '{"prop": "value"}'::OBJECT(IGNORED))

How to you end up with an OBJECT(IGNORED) in the table schema?
I think this is mapped to OBJECT(DYNAMIC)

i.e.

CREATE TABLE IF NOT EXISTS "doc"."event" (
   "id" BIGINT,
   "ts" BIGINT,
   "obj" OBJECT(DYNAMIC) AS (
      "prop" TEXT
   )
)

Just to be clear, you want to have multiple OBJECT(IGNORED) columns i.e. obj1, obj2, … and have them dynamically created? I don’t think this is currently supported.

An alternative might be to specify a collection object:

CREATE TABLE IF NOT EXISTS "doc"."event" (
   "id" BIGINT,
   "ts" BIGINT,
   "obj" OBJECT(IGNORED)
)

and then insert into a property. e.g.:

{
  "stmt": "INSERT INTO event (id, ts, obj) values(?, ?, {obj1 = ?})",
  "bulk_args": [
    [1, 1667842489642, {"prop": "value"}]
  ]
}

you could then (later) add the column also
(e.g.

ALTER TABLE doc.event ADD COLUMN obj['obj1'] OBJECT(IGNORED);

CREATE TABLE IF NOT EXISTS "doc"."event" (
   "id" BIGINT,
   "obj" OBJECT(IGNORED) AS (
      "obj1" OBJECT(IGNORED)
   ),
   "ts" BIGINT
)
1 Like
3

@proddata, yeah, my bad, it also adds a dynamic object. Thanks for the response