postgresql partitioning by date from existing table

Question

I need to partition an existing table which is main.customer by year. The situation I have is that, I have a column that contains a string with year in it and I want to extract the year part from that string.

my sample query is..

CREATE TABLE main.customer_prtn (LIKE main.customer)
PARTITION BY RANGE (to_date(substring(ref_id,1,4),'YYYY'))
(START (date '2008') INCLUSIVE,
....
END (date '2015') EXCLUSIVE
EVERY (INTERVAL '1 year') );

When I run the query, i get the message

ERROR:  syntax error at or near "("
LINE 2: PARTITION BY RANGE (to_date(substring(ref_id,1,4),'YYYY'))

You really should clarify your question!

user5087998
– user5087998

2016-12-07 10:59:30 +00:00
Commented Dec 7, 2016 at 10:59 — user5087998
– user5087998, Commented Dec 7, 2016 at 10:59

Laurenz Albe · Accepted Answer · 2016-12-07 11:00:15Z

1

PostgreSQL doesn't have declarative partitioning yet (a patch for v10 is under review).

Read the documentation about partitioning to find out how it has to be done currently.

answered Dec 7, 2016 at 11:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql partitioning by date from existing table

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