CODEWITHSUNDEEP
postgresqldatabase-partitioningpostgresql-10
user275801
user275801

Reputation: 1355

Postgres table partition range by expression

I'm new to table partition in Postgres and I was trying something which didn't seem to work. Here's a simplified version of the problem:

cREATE table if not exists Location1 
PARTITION OF "Location"
FOR VALUES FROM
(extract(epoch from now()::timestamp))  
TO 
(extract(epoch from now()::timestamp)) ;

insert into "Location" ("BuyerId", "Location") 
values (416177285, point(-100, 100));

It fails with:

ERROR:  syntax error at or near "extract"
LINE 2:     FOR VALUES FROM (extract(epoch from now()::timestamp))

If I try to create the partition using literal values, it works fine.

I'm using Postgres 10 on linux.

Upvotes: 0

Views: 1584

Answers (1)

user275801
user275801

Reputation: 1355

After a lot of trial and error, I realized that Postgres 10 doesn't really have a full partitioning system out of the box. For a usable partitioned database, the following postgres modules are necessary to be installed:

pg_partman https://github.com/pgpartman/pg_partman (literally a gift from God)

pg_jobmon https://github.com/omniti-labs/pg_jobmon (to monitor what pg_partman is doing)

The above advice is for those new to the scene, to help you avoid a lot of headaches.

I assumed that automatic creation of partitions done by postgres-core was a no-brainer, but evidently the postgres devs know something I don't?

If any relational database from the 80's is asked to insert a row, it succeeds after passing the constraint checks.

The latest version of Postgres 2018 is asked to insert a row: "Sorry, I don't know where to put it."

It's a good first effort, but hopefully Postgres 11 will have a proper partitioning system OOTB...

Upvotes: 1

Related Questions