Skip to content

Commit fe18cfa

Browse files
zy-kkkzy-kkk
authored
[improvement](pg jdbc)Support for automatically obtaining the precision of the postgresql timestamp type (apache#20909)
1 parent 367f64e commit fe18cfa

5 files changed

Lines changed: 24 additions & 8 deletions

File tree

‎docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -160,4 +160,9 @@ CREATE TABLE catalog_pg_test.test_insert (
160160
CREATE TABLE catalog_pg_test.wkb_test (
161161
id SERIAL PRIMARY KEY,
162162
location bytea
163-
);
163+
);
164+
165+
CREATE TABLE catalog_pg_test.dt_test (
166+
ts_field TIMESTAMP(3),
167+
tzt_field TIMESTAMPTZ(3)
168+
);

‎docker/thirdparties/docker-compose/postgresql/init/04-insert.sql‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2656,4 +2656,11 @@ insert into catalog_pg_test.test12 values
26562656
insert into catalog_pg_test.test12 values
26572657
(2, '980dd890-f7fe-4fff-999d-873516108b2e');
26582658

2659-
INSERT INTO catalog_pg_test.wkb_test (location) SELECT ST_AsBinary(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
2659+
INSERT INTO catalog_pg_test.wkb_test (location) SELECT ST_AsBinary(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
2660+
2661+
INSERT INTO catalog_pg_test.dt_test (ts_field, tzt_field)
2662+
VALUES
2663+
(
2664+
'2023-06-16 12:34:56.123',
2665+
'2023-06-16 12:34:56.123+08'
2666+
);

‎fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcPostgreSQLClient.java‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -67,7 +67,12 @@ protected Type jdbcTypeToDoris(JdbcFieldSchema fieldSchema) {
6767
case "timestamp":
6868
case "timestamptz":
6969
// postgres can support microsecond
70-
return ScalarType.createDatetimeV2Type(JDBC_DATETIME_SCALE);
70+
int scale = fieldSchema.getDecimalDigits();
71+
if (scale < 6) {
72+
return ScalarType.createDatetimeV2Type(scale);
73+
} else {
74+
return ScalarType.createDatetimeV2Type(JDBC_DATETIME_SCALE);
75+
}
7176
case "date":
7277
return ScalarType.createDateV2Type();
7378
case "bool":

‎regression-test/data/jdbc_catalog_p0/test_pg_jdbc_catalog.out‎

Lines changed: 3 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -5,11 +5,6 @@
55
234 bcd
66

77
-- !in_tb --
8-
111 abc
9-
112 abd
10-
113 abe
11-
114 abf
12-
115 abg
138
123 abc
149
123 abc
1510
234 bcd
@@ -2144,6 +2139,9 @@ true abc def 2022-10-11 1.234 1 2 99 2022-10-22T10:59:59 34.123
21442139
-- !wkb_test --
21452140
1 \\x01030000000100000005000000000000000000000000000000000000000000000000000000000000000000f03f000000000000f03f000000000000f03f000000000000f03f000000000000000000000000000000000000000000000000
21462141

2142+
-- !dt_test --
2143+
2023-06-16T12:34:56.123 2023-06-16T12:34:56.123
2144+
21472145
-- !test_insert1 --
21482146
doris1 18
21492147

‎regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,7 @@ suite("test_pg_jdbc_catalog", "p0") {
7070
order_qt_test13 """ select * from test11 order by id; """
7171
order_qt_test14 """ select * from test12 order by id; """
7272
order_qt_wkb_test """ select * from wkb_test order by id; """
73+
order_qt_dt_test """ select * from dt_test order by 1; """
7374

7475
// test insert
7576
String uuid1 = UUID.randomUUID().toString();

0 commit comments

Comments
 (0)