Syntax error at or near "INTERVAL" while creating PARTITIONed table in PG

Question

I am trying to create a partitioned table in edb 9.6.10 . I am getting the following error when I am trying to do so

    edb=# create table employee (id int, join_date timestamp(6)) PARTITION BY RANGE (join_date) INTERVAL ('1 day'::interval);
    ERROR:  syntax error at or near "INTERVAL"
    LINE 1: ...date timestamp(6)) PARTITION BY RANGE (join_date) INTERVAL (...
                                                             ^

Please let me know what I am doing wrong here.

This must be something different from PostgreSQL partitioning. I recommend that you upgrade and use that. — Laurenz Albe, Apr 06 '21 at 13:48
even in postgresql you can't have 'INTERVAL' for table partitioning like that if I'm not mistaken — eshirvana, Apr 06 '21 at 15:36

score 0 · Answer 1 · answered Apr 06 '21 at 15:49

0

As far as I can tell, the feature you are trying to use only exists in EDB's paid product, and even there it only became available in v12.

answered Apr 06 '21 at 15:49

jjanes

37,812
5
27
34

Is it available with postgres 13 ? – Jackson Apr 06 '21 at 15:53
community PostgreSQL 13? No. EDB's proprietary product? I would assume so, but have no direct knowledge. – jjanes Apr 06 '21 at 16:34

score 0 · Answer 2 · answered Apr 12 '21 at 16:59

This PARTITION BY RANGE INTERVAL is only supported from version 12 and onward.

See the difference of behaviour for the same query in version11 and version12.

edb=# select version;
 PostgreSQL 11.11 (EnterpriseDB Advanced Server 11.11.20) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

edb=# create table employee (id int, join_date timestamp(6)) PARTITION BY RANGE (join_date) INTERVAL ('1 day'::interval);
ERROR:  syntax error at or near "INTERVAL"
LINE 1: ...date timestamp(6)) PARTITION BY RANGE (join_date) INTERVAL (...

and in Version12 we get

edb=# select version();
PostgreSQL 12.4 (EnterpriseDB Advanced Server 12.4.5) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
    (1 row)

edb=# create table employee (id int, join_date timestamp(6)) PARTITION BY RANGE (join_date) INTERVAL ('1 day'::interval);
CREATE TABLE

Moreover, this 'INTERVAL' syntax is only available in EnterpriseDB paid product. In the case of community Postgres e.g V13 you will still get the same error.

[root@localhost bin]# ./psql -U postgres -d postgres -p 5432
psql (13.2)

Type "help" for help.

postgres=# create table employee (id int, join_date timestamp(6)) PARTITION BY RANGE (join_date) INTERVAL ('1 day'::interval);
ERROR:  syntax error at or near "INTERVAL"
LINE 1: ...date timestamp(6)) PARTITION BY RANGE (join_date) INTERVAL (...

Syntax error at or near "INTERVAL" while creating PARTITIONed table in PG

2 Answers2