Database result transformations for better PostgreSQL compatibility

[amotl]

Dear @florinutz,

as I can see you've used more advanced dlt features (normalizers) in your other patch GH-49 than I have ever been able to learn, this request might be equally easy to pull off for you: Within the patch referenced below, you can find another good chunk of integration tests of the popular ingestr ETL framework against CrateDB within the ingestr/main_test.py file, see diff.

• Tests: Adjust software tests for CrateDB bruin-data/ingestr#284

Unfortunately, the patch never landed, mostly because it wasn't really ready for merging, because it included constructs like this:

# Compensate for CrateDB types and insert order.
if dest_connection_url.startswith("cratedb://"):
    assert (1, "val1", 1640995200000) in res
    assert (2, "val2", 1643673600000) in res
else:
    assert res[0] == (1, "val1", as_datetime("2022-01-01"))
    assert res[1] == (2, "val2", as_datetime("2022-02-01"))

For everyone using CrateDB, the difference is obvious. Everyone else might think "what the ....?". In this spirit, it would be so cool to bring a corresponding dlt component into the mix that could do the job of converting datetime fields so that dlt does not "see" any difference to PostgreSQL in this regard. Do you think it is possible to wrap this into dlt-cratedb so it becomes a no-brainer for the average user?

With kind regards,
Andreas.

[amotl]

Do you think it is possible to wrap this into dlt-cratedb so it becomes a no-brainer for the average user?

Actually, crate-python offers a similar add-on feature, see The Cursor object » Python data type conversion. However, it can't be used here because the low-level driver is psycopg2 in this case, coming from dlt[postgres].

[amotl]

Hi @florinutz. We referred to PostgreSQL-compatible CrateDB test suite support on another conversation.

• Iterable Data panodata/omniload#45 (comment)

Maybe you know a trick up your sleeve how to pull off that TIMESTAMP value conversion swiftly, using native dlt paradigms like normalizers you've used the other day? Optimally, it would be just a few lines of code that could unlock our refurbishments on bruin-data/ingestr#284.

[amotl]

@coderabbitai: Can you support us here?

[coderabbitai]

Tip

For best results, initiate chat on the files or code changes.

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
set -euo pipefail
fd . -t d -d 2
echo "---"
fd . -e py | xargs -I{} echo {}

Length of output: 1281

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail
cat -n src/dlt_cratedb/impl/cratedb/sql_client.py
echo "=== cratedb.py ==="
cat -n src/dlt_cratedb/impl/cratedb/cratedb.py
echo "=== factory.py ==="
cat -n src/dlt_cratedb/impl/cratedb/factory.py
echo "=== utils.py ==="
cat -n src/dlt_cratedb/impl/cratedb/utils.py

Length of output: 21060

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail
python - <<'EOF'
import importlib, inspect
mod = importlib.import_module("dlt.destinations.impl.postgres.sql_client")
print(inspect.getsourcefile(mod))
EOF

Length of output: 2197

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail
pip show dlt 2>/dev/null || pip install "dlt[postgres]" -q 2>&1 | tail -20
python - <<'EOF'
import importlib, inspect
mod = importlib.import_module("dlt.destinations.impl.postgres.sql_client")
print(inspect.getsourcefile(mod))
print(inspect.getsource(mod))
EOF

Length of output: 6778

---

🤖 web_search executed:

CrateDB PostgreSQL wire protocol TIMESTAMP column returned as epoch milliseconds psycopg2

Length of output: 4180

---

🤖 web_search executed:

crate/crate PGTypes.java TIMESTAMP OID postgres wire protocol crate source code

Length of output: 9106

---

🤖 web_search executed:

CrateDB postgres.rst "typoutput" OR "D" column meaning pg_type table timestamp date epoch milliseconds output format

Length of output: 2873

---

@amotl Good news — this looks solvable with a small, self-contained addition to CrateDbSqlClient, and it doesn't need a dlt "normalizer" at all, since normalizers only run on the load path (before/at ingestion), not on query results coming back from execute_sql() / execute_query().

Here's the underlying mechanism: CrateDB's PostgreSQL wire protocol correctly reports the canonical PostgreSQL OIDs for temporal columns (1114 for timestamp, 1184 for timestamptz), but the value it serializes for those OIDs is the raw number of milliseconds since the Unix epoch rather than an ISO-8601-formatted string. Since psycopg2 looks up its typecaster purely by OID, and CrateDB's payload doesn't match what the built-in timestamp typecaster expects, the value effectively passes through untouched as a plain integer — exactly the 1640995200000 you're seeing.

Because we already have a natural extension point in dlt_cratedb.impl.cratedb.sql_client.CrateDbSqlClient.open_connection() (it already customizes the psycopg2 connection to fix up search_path), we can register a connection-scoped psycopg2 typecaster there for OIDs 1114/1184 that converts the epoch-millisecond integer into a proper datetime:

from datetime import datetime, timezone

from psycopg2.extensions import new_type, register_type

# CrateDB's PostgreSQL wire protocol reports the canonical PostgreSQL OIDs for
# TIMESTAMP (1114) and TIMESTAMPTZ (1184), but serializes the value as the raw
# number of milliseconds since the Unix epoch instead of an ISO-8601 string.
# https://cratedb.com/docs/crate/reference/en/latest/interfaces/postgres.html
_CRATEDB_TIMESTAMP_OIDS = (1114, 1184)


def _cast_cratedb_timestamp(value, cursor):
    if value is None:
        return None
    return datetime.fromtimestamp(int(value) / 1000, tz=timezone.utc)


_CRATEDB_TIMESTAMP_TYPE = new_type(
    _CRATEDB_TIMESTAMP_OIDS, "CRATEDB_TIMESTAMP", _cast_cratedb_timestamp
)


class CrateDbSqlClient(Psycopg2SqlClient):
    ...

    def open_connection(self) -> "psycopg2.connection":
        self._conn = psycopg2.connect(
            dsn=self.credentials.to_native_representation(),
            options=f"-c search_path={self.fully_qualified_dataset_name()},doc",
        )
        # Compensate for CrateDB emitting epoch-millisecond integers instead of
        # ISO-8601 text for TIMESTAMP/TIMESTAMPTZ columns.
        register_type(_CRATEDB_TIMESTAMP_TYPE, self._conn)
        self._reset_connection()
        return self._conn

A few notes/trade-offs worth flagging:

• Registering register_type(..., self._conn) scopes the typecaster to this one connection object, so it won't leak into other code in the same process that talks to genuine PostgreSQL via psycopg2 (important since dlt[postgres] is a shared dependency).
• All CrateDB TIMESTAMP/TIMESTAMP WITH TIME ZONE values are UTC internally, so hardcoding tzinfo=timezone.utc in the conversion is safe and matches what crate-python's own converter does.
• CrateDB doesn't support a storable TIME type in dlt-cratedb's type mapper already (see CrateDbTypeMapper), and DATE wasn't storable in CrateDB until fairly recently (Support PostgreSQL DATE as a storable column type crate#19588), so OIDs 1082/1083 are lower priority, but could be added the same way later if needed.
• This is complementary to (and doesn't conflict with) the escaping/type-mapping work already in utils.py/factory.py — this only affects the read path (SELECT results), not writes.

I'd be glad to turn this into a PR against dlt-cratedb with an accompanying test (e.g., extending tests/load/cratedb/test_cratedb_client.py) if that's useful — just let me know and I can kick off the implementation.

---

✏️ Learnings added

Learnt from: amotl
URL: https://github.com/crate/dlt-cratedb/issues/54

Timestamp: 2026-07-07T14:41:20.864Z
Learning: In the `crate/dlt-cratedb` repository, `CrateDbSqlClient` (src/dlt_cratedb/impl/cratedb/sql_client.py) wraps dlt's `Psycopg2SqlClient`. CrateDB's PostgreSQL wire protocol reports the canonical PostgreSQL OIDs 1114 (timestamp) and 1184 (timestamptz) for temporal columns, but serializes the value as raw epoch-milliseconds instead of ISO-8601 text, so `psycopg2` returns plain integers instead of `datetime` objects for these columns. The fix belongs in `CrateDbSqlClient.open_connection()` by registering a connection-scoped psycopg2 typecaster (via `psycopg2.extensions.new_type`/`register_type`) for OIDs 1114/1184 that converts epoch-millisecond integers into UTC `datetime` objects. This is a read-path concern (SELECT results), so dlt "normalizers" (which only run on the load path) are not the right tool for it.

Note: Learnings are effective only in the context of similar code segments. To apply general coding guidelines, please configure review instructions. You can manage existing learnings in the app.

[amotl]

Can improvements like this help to make CrateDB more compatible with PostgreSQL in the area of datetime support? /cc @coderabbitai

• PostgreSQL compatibility: AAUIW support for storing data using the Postgres DATE data type crate#19414

[coderabbitai]

Tip

For best results, initiate chat on the files or code changes.

🧩 Analysis chain

🔍 Additional research query:

What is GitHub issue crate/crate#19414 about?

Length of output: 71

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail
gh issue view 19414 --repo crate/crate --json title,body,state,labels,comments 2>&1 | head -200

Length of output: 12444

---

@amotl Yes, this is a helpful and complementary development — though it's server-side and doesn't directly fix the TIMESTAMP/TIMESTAMPTZ epoch-milliseconds issue we're compensating for in CrateDbSqlClient.open_connection().

Here's how #19414 connects to the broader compatibility story:

1. It's about storage, not the wire-protocol serialization bug. #19414 fixes DateType.storageSupport() returning null, which previously made CREATE TABLE t (d DATE) fail outright with UnsupportedOperationException. It stores DATE as long epoch-millis normalized to midnight UTC — reusing the same in-memory representation and cast machinery as TIMESTAMP. Once this lands, dlt-cratedb's CrateDbTypeMapper could map dlt's date column type to a native CrateDB DATE column instead of falling back to TIMESTAMP (a limitation I mentioned earlier, since DATE wasn't storable at all until now).

2. A related but distinct binary-protocol bug was fixed alongside it. During review, mfussenegger found that CrateDB's DateType on the PostgreSQL wire protocol (OID 1082) inherited BaseTimestampType.writeAsBinary, emitting 12 bytes (int64 microseconds) instead of the PG-spec 8 bytes (int32 days since 2000-01-01 per date_send). That's fixed (and backported) in Fix encoding and decoding of date values via pgwire crate#19513. This is structurally the same category of bug as the one we're compensating for in dlt-cratedb — a wire-protocol value not matching what psycopg2/PostgreSQL clients expect for a given OID — just on the binary path for DATE rather than the text path for TIMESTAMP/TIMESTAMPTZ.

3. What this means for our workaround: the two issues don't overlap in the affected OIDs (1082 for DATE vs. 1114/1184 for TIMESTAMP/TIMESTAMPTZ), and text-mode psycopg2 connections (as used by dlt[postgres]) typically negotiate the text protocol rather than binary, so #19513's binary fix likely doesn't change what we see for TIMESTAMP text-mode results. But it's a good signal that CrateDB is actively closing these PG-wire compatibility gaps upstream — the "real" fix, versus client-side typecaster workarounds like ours.

Once #19414 is fully released, it would be worth:

• Adding native DATE support to CrateDbTypeMapper in dlt-cratedb (rather than mapping to TIMESTAMP), and
• Verifying whether CrateDB's text-protocol serialization for DATE (OID 1082) also needs a typecaster like the one we added for OIDs 1114/1184 — if so, extending _CRATEDB_TIMESTAMP_OIDS-style registration to cover it as well.

Happy to track that as a follow-up once the CrateDB release containing #19414 is out, if you'd like me to open a tracking issue in dlt-cratedb for it.

---

✏️ Learnings added

Learnt from: amotl
URL: https://github.com/crate/dlt-cratedb/issues/54

Timestamp: 2026-07-07T14:49:33.077Z
Learning: Learnt from: amotl
URL: https://github.com/crate/dlt-cratedb/issues/54

CrateDB issue crate/crate#19414 ("PostgreSQL compatibility: support for storing data using the Postgres DATE data type") is CLOSED/implemented: `DateType` (id=24) in CrateDB already had PG wire OID 1082 wired up, but `storageSupport()` returned null; the fix adds `StorageSupport<Long>` for DateType, storing DATE as long epoch-millis normalized to midnight UTC (reusing TIMESTAMP's in-memory representation/cast machinery). Scope was limited to storage + EqQuery comparisons; DATE arithmetic/functions are out of scope. A related binary-wire-protocol bug (DateType incorrectly inheriting BaseTimestampType.writeAsBinary, emitting 12 bytes instead of PG-spec 8 bytes for date_send) was fixed in crate/crate#19513. For `dlt-cratedb`, once this CrateDB release ships, `CrateDbTypeMapper` (src/dlt_cratedb/impl/cratedb/utils.py or factory.py) could map dlt's `date` type to native CrateDB DATE instead of falling back to TIMESTAMP, and the text-protocol serialization for DATE (OID 1082) should be checked to see if it needs the same epoch-millis-to-datetime typecaster workaround already added for TIMESTAMP/TIMESTAMPTZ (OIDs 1114/1184) in `CrateDbSqlClient.open_connection()`.

Note: Learnings are effective only in the context of similar code segments. To apply general coding guidelines, please configure review instructions. You can manage existing learnings in the app.

[amotl]

I'd be glad to turn this into a PR against dlt-cratedb with an accompanying test (e.g., extending tests/load/cratedb/test_cratedb_client.py) if that's useful — just let me know and I can kick off the implementation.

@coderabbitai: Yes, please submit a corresponding PR. Thank you.

[coderabbitai]

Oops, something went wrong! Please try again later. 🐰 💔

[amotl]

The anomaly became a non-issue with CrateDB nightly that includes a relevant improvement by @DHRUV6029. Thank you so much. 👍

• PostgreSQL compatibility: AAUIW support for storing data using the Postgres DATE data type crate#19414
• Support PostgreSQL DATE as a storable column type crate#19588
• Tests: Add integration tests with CrateDB panodata/omniload#192

/cc @srmadscience, @florinutz, @coderabbitai