Connector Name

destination-postgres

Connector Version

3.0.13

What step the error happened?

During the sync

Relevant information

Destination Postgres fails to load PostgreSQL BC dates after JDBC/Java represents them as ISO proleptic years.

Java java.time includes year zero and negative years, while PostgreSQL uses year-of-era notation with an explicit BC marker and does not accept year zero.

Destination Postgres currently writes these Java representations directly to CSV. PostgreSQL COPY then rejects them with datetime_field_overflow.

Expected behavior

Before CSV COPY, Destination Postgres should format non-positive Java proleptic years using PostgreSQL year-of-era notation.

Timestamp fields must retain their time, fractional seconds, and timezone offset:

0000-07-01T12:34:56.123456
→ 0001-07-01 12:34:56.123456 BC

0000-07-01T12:34:56.123456+07:00
→ 0001-07-01 12:34:56.123456 BC +07:00

Root cause

Java and PostgreSQL use different calendar representations:

Java year  1 → PostgreSQL 0001 AD
Java year  0 → PostgreSQL 0001 BC
Java year -1 → PostgreSQL 0002 BC
Java year -2 → PostgreSQL 0003 BC

For non-positive Java years:

PostgreSQL year-of-era = 1 - Java proleptic year

Removing only the minus sign would change the date semantics. Java -0001 must become PostgreSQL 0002 BC, not 0001 AD.

Steps to reproduce

Create test data in PostgreSQL:

CREATE TABLE airbyte_bc_source (
    id integer PRIMARY KEY,
    date_value date,
    timestamp_value timestamp,
    timestamptz_value timestamptz
);

INSERT INTO airbyte_bc_source
VALUES (
    1,
    DATE '0001-07-01 BC',
    TIMESTAMP '0001-07-01 12:34:56.123456 BC',
    TIMESTAMPTZ '0001-07-01 12:34:56.123456 BC +07:00'
);

Configure a PostgreSQL-to-PostgreSQL Airbyte connection using typed destination columns and run the sync.

PostgreSQL rejects the Java representation:

SELECT '0000-07-01'::date;

PostgreSQL accepts the semantically equivalent representation:

SELECT '0001-07-01 BC'::date;

The timestamp-with-timezone representation is also accepted:

SELECT '0001-07-01 12:34:56.123456 BC +07:00'::timestamptz;

Proposed fix

Add PostgreSQL-specific temporal formatting in PostgresRecordFormatter.kt:

year > 0
  → preserve current behavior
  → remove the leading "+" from extended positive years

year <= 0
  → calculate yearOfEra = 1 - year
  → preserve month, day, time, fractional seconds, and offset
  → append the PostgreSQL BC marker

Keep the conversion in Destination Postgres rather than the generic CSV CDK because the output syntax is PostgreSQL-specific.

Regression coverage should include DateValue, TimestampWithoutTimezoneValue, TimestampWithTimezoneValue, normal AD years, extended positive years, year zero, and negative proleptic years.

Impact

A single unsupported BC value can fail an entire buffered COPY batch, affecting otherwise valid rows in that batch.

In the observed destination-postgres:3.0.13 runtime, the COPY exception was logged but not propagated. Later batches continued and the sync eventually reported a completed status. This exception-handling behavior increases the impact but is separate from the calendar-formatting bug.

Related issue

Related to #14590, but not a duplicate.

Issue #14590 concerns Source Postgres CDC/Debezium losing the BC era. This issue concerns Destination Postgres failing to translate Java ISO proleptic years into PostgreSQL-compatible BC notation before CSV COPY.

Relevant log output

org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "0000-07-01"
Where: COPY <temporary_table>, line 1541, column <date_column>: "0000-07-01"
SQLSTATE: 22008

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(...)
at org.postgresql.copy.CopyManager.copyIn(...)
at io.airbyte.integrations.destination.postgres.client.PostgresAirbyteClient.copyFromCsv(...)
at io.airbyte.integrations.destination.postgres.write.load.PostgresInsertBuffer.flush(PostgresInsertBuffer.kt:60)

Unable to flush accumulated data.

org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "0000-07-26"
Where: COPY <temporary_table>, line 21126, column <date_column>: "0000-07-26"
SQLSTATE: 22008

Contribute

• Yes, I want to contribute