Created_at not populated with default value during insert in CrateDB via C#

CrateDB SQL
1 
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        TIMESTAMP,   -- from IngestTs

    value_data OBJECT(DYNAMIC) AS (
        float_value DOUBLE PRECISION,
        int_value   BIGINT,
        bool_value  BOOLEAN,
        text_value  TEXT,
        json_value  TEXT
    ),

    partition_key    TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('month', tag_ts),
    created_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (object_id, tag_name, tag_ts, partition_key)
)
CLUSTERED INTO 4 SHARDS
PARTITIONED BY (partition_key)
WITH (number_of_replicas = 1);

Insert Statement (via Npgsql C# connector)

INSERT INTO symx_telemetry_opt_default_issue
(object_id, element_type_id, tag_name, tag_ts, ingest_ts, value_data)
VALUES {string.Join(", ", insertValues)}
ON CONFLICT (object_id, tag_name, tag_ts, partition_key)
DO UPDATE SET
value_data = EXCLUDED.value_data,
ingest_ts = EXCLUDED.ingest_ts;

Problem

  • The column created_at is defined with DEFAULT CURRENT_TIMESTAMP.

  • When inserting new rows (no conflict), created_at is still NULL.

  • On conflict updates, created_at also remains NULL.

Expected Behavior

  • On fresh inserts (no conflict), created_at should be automatically populated with the current timestamp from DEFAULT CURRENT_TIMESTAMP.

  • On conflict updates, created_at should retain its previously set value and not be reset to NULL.

Notes

This behavior was not observed in previous CrateDB versions.
Previous version used : 5.9.13
Current version : 5.10.11
Npgsql : 9.0.3

  • The default expression (DEFAULT CURRENT_TIMESTAMP) seems to be ignored entirely when inserting through the Npgsql connector.
1 Like
2

Hi Ayas,

We have identified this issue in version 5.10.11 already and the fix is on the making, check it here Generated timestamp column becomes NULL when updating records · Issue #18219 · crate/crate · GitHub - even though the bug description refers to the generated column, you may find in the comments a reference to `DEFAULT` values as well. I’d suggest you follow the issue in Github to get the latest developments.

In summary, this is a bug in CrateDB related to the `UPDATE` statement, unrelated to Npgsql.

Thank you,

Karyn Azevedo

1 Like