Postgres table partition range by expression

Question

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.

Why don't you store your timestamps as a real timestamp? Then you don't need this hack to extract the epoch — user330315
– user330315, Commented Aug 31, 2018 at 5:50
Please edit your question and add the create table statement for the partitioned table — user330315
– user330315, Commented Aug 31, 2018 at 5:50

user275801 · Accepted Answer · 2018-09-01 07:00:51Z

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...

Collectives™ on Stack Overflow

Postgres table partition range by expression

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related