postgresql 波斯特格雷斯11.9:按tstzrange分区

bweufnob  于 2022-11-23  发布在  PostgreSQL
关注(0)|答案(1)|浏览(116)

我需要按tstzrange列对表进行范围分区。找不到任何示例

CREATE TABLE schedule_slots
(
    id                     int8        NOT NULL DEFAULT nextval('schedule_slots_id_seq'::regclass),
    time_range             tstzrange   NOT NULL,
    CONSTRAINT schedule_slots_pkey2 PRIMARY KEY (id, time_range)
) partition by range(time_range);

正在尝试创建如下分区

CREATE TABLE schedule_slots_part1 PARTITION OF schedule_slots
    FOR VALUES FROM tstzrange('2022-10-01 00:00:00-07','2022-10-01 23:59:59-07', '[)') to tstzrange('2023-10-01 00:00:00-07','2023-10-01 23:59:59-07', '[)')

目的是将2022-10-1和2023-10-1之间的所有行写入此分区。但是,分区创建失败,错误为

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "tstzrange"
  Position: 83
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:327)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)
    ... 12 more

请告诉我如何基于tstzrange列对表进行分区

dced5bon

dced5bon1#

CREATE TABLE schedule_slots_part1 PARTITION OF schedule_slots
    FOR VALUES FROM ('[2022-10-01 00:00:00-07,2022-10-01 23:59:59-07)') to ('[2023-10-01 00:00:00-07,2023-10-01 23:59:59-07)');

正在工作!!

相关问题