Skip to content

[Fix][Connectors-Jdbc] Postgres supports streaming and batch reading and writing of the interval data type #9590

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
Hisoka-X merged 6 commits into from
Jul 22, 2025
Merged

[Fix][Connectors-Jdbc] Postgres supports streaming and batch reading and writing of the interval data type #9590

Hisoka-X merged 6 commits into from
Jul 22, 2025
+168 −0

Conversation

@chestnut-c
Copy link
Contributor

@chestnut-c chestnut-c commented Jul 17, 2025 •
edited
Loading

Purpose of this pull request

Postgres supports interval data type for reading and writing. close #9582

Does this PR introduce any user-facing change?

No

How was this patch tested?

for batch task:
The interval data type is not supported before modification
企业微信截图_f3441053-f801-4fbf-9209-fea2a7483c3e

After modification, the interval data type can be read and written normally.

Clipboard_Screenshot_1752762697 Clipboard_Screenshot_1752762722 Clipboard_Screenshot_1752762678

for streaming task:
And it is compatible with cdc scenarios. The Postgres interval type is converted to microseconds (long type) in Debezium such as io.debezium.time.MicroDuration , so if it is an number, it is given in microseconds by default.
Clipboard_Screenshot_1753002878
Clipboard_Screenshot_1753003301

Check list

@github-actions github-actions bot added the e2e label Jul 18, 2025
Postgres cdc interval types are converted to microseconds (long type)…
f0c99ba 
… in Debezium, so if it is an integer, it is given microseconds by default.
@chestnut-c chestnut-c requested a review from Hisoka-X July 20, 2025 09:22
@chestnut-c chestnut-c changed the title [Fix][Connectors-Jdbc] Postgres supports interval data type for reading and writing. [Fix][Connectors-Jdbc] Postgres supports streaming and batch reading and writing of the interval data type Jul 20, 2025
chestnufang added 2 commits July 21, 2025 10:17
fix unit test case task config
4aef234
postgres interval types are converted to microseconds (long) in Debez…
15a763d 
…ium, so if it is a number, it is formatted as a postgres interval value.
@WJX20
Copy link

WJX20 commented Jul 21, 2025

Your way of writing is not quite correct. You directly converted the read-out microsecond numbers into "strings". Debezium's configuration provides an 'interval.handling.mode' attribute. Seatunnel should retain this attribute. When reading the Debezium configuration, the user can choose whether to select 'string' or 'number'.
interval

@WJX20
Copy link

WJX20 commented Jul 21, 2025

Moreover, your approach only covers the "day" type. The "interval" type should also include "month" and "year".
The processing method for the interval data type is that the default value is 365.25 days for a year, and 365.25/12 = 30.4375 days for a month.

@chestnut-c
Copy link
Contributor Author

chestnut-c commented Jul 21, 2025 •
edited
Loading

Debezium

@WJX20 Yes, I am dealing with the default number type when the 'interval.handling.mode parameter is not used. Since Debezium does not retain the format when reading, in order to make the data calculation more accurate, it is only formatted to the day. Even if it is calculated to the year and month, it is still impossible to guarantee the same format. As long as the interval data is consistent, the format does not have to be the same. other ,If you need the same format, you can try using the interval.handling.mode parameter to get the interval data in string format containing year, month, and day.

@chestnut-c
Copy link
Contributor Author

chestnut-c commented Jul 21, 2025 •
edited
Loading

interval.handling.mode

Clipboard_Screenshot_1753091513

The default type currently processed is io.debezium.time.MicroDuration
The default type is to keep the accuracy as much as possible and convert it to day format by default.u can use this way debezium={"interval.handling.mode":"string"} to support setting the interval.handling.mode parameter to keep the original format io.debezium.time.Interval
Clipboard_Screenshot_1753091623

@WJX20
Copy link

WJX20 commented Jul 21, 2025

got off work

@chestnut-c
Copy link
Contributor Author

chestnut-c commented Jul 21, 2025 •
edited
Loading

got off work

@WJX20 now you can use this configuration parameter interval.handling.mode，I think it is supported, the test is OK, the format has been preserved, u can use this way debezium={"interval.handling.mode":"string"} to support setting the interval.handling.mode parameter
Clipboard_Screenshot_1753093008
企业微信截图_49f439c6-e018-4a30-aa67-c9ed7dcfbc85

企业微信截图_bfbccb7a-545c-4a47-aa49-2dc666de5083

Hisoka-X
Hisoka-X previously approved these changes Jul 21, 2025
View reviewed changes
Copy link
Member

@Hisoka-X Hisoka-X left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM. Thanks @chestnut-c

@WJX20
Copy link

WJX20 commented Jul 22, 2025

ok，Let me test it. The naming of 'inetObject' is not standard. This is of the 'INET' data type.
intervalObject

@chestnut-c
Copy link
Contributor Author

chestnut-c commented Jul 22, 2025

ok，Let me test it. The naming of 'inetObject' is not standard. This is of the 'INET' data type. intervalObject

ok i will fix it next time

@corgy-w
Copy link
Contributor

corgy-w commented Jul 22, 2025

@chestnut-c I changed the variable name and it's OK. If I wait a moment and merge it

corgy-w
corgy-w approved these changes Jul 22, 2025
View reviewed changes
Copy link
Contributor

@corgy-w corgy-w left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

wait ci

@chestnut-c
Copy link
Contributor Author

chestnut-c commented Jul 22, 2025

@chestnut-c I changed the variable name and it's OK. If I wait a moment and merge it

ok thx @corgy-w

@Hisoka-X Hisoka-X merged commit 58ab917 into apache:dev Jul 22, 2025
6 checks passed
dybyte pushed a commit to dybyte/seatunnel that referenced this pull request Jul 23, 2025
@chestnut-c @corgy-w @dybyte
[Fix][Connectors-Jdbc] Postgres supports streaming and batch reading …
ab7abb5 
…and writing of the `interval` data type (apache#9590)

Co-authored-by: chestnufang <[email protected]>
Co-authored-by: corgy-w <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@Hisoka-X Hisoka-X Hisoka-X approved these changes

@corgy-w corgy-w corgy-w approved these changes

Assignees

No one assigned

Labels

approved connectors-v2 e2e jdbc reviewed

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

[Bug] [seatunnel-connectors-v2] Not support the pg "interval" data type

4 participants

@chestnut-c @WJX20 @corgy-w @Hisoka-X