0

i want to partition my table. the table is:

create_table "multimedia", :force => true do |t|     
   t.integer   "id"   
   t. binary   "data"    
   t.integer   "device_id"         
   t.datetime  "created_at"                  
end

id is the primary key
created_at is a simple index for the table and there isn't unique

i need to partition the table by the field created_at. In my table there's only record from the last 7 days so i want to partition it by the day. i write this cose:

partition by range (to_days(created_at))          

  ( PARTITION p20120417 VALUES LESS THAN (to_days('2012-04-17 23:59:59 +0200')),                   
    PARTITION p20120418 VALUES LESS THAN (to_days('2012-04-18 23:59:59 +0200')),                
    PARTITION p20120419 VALUES LESS THAN (to_days('2012-04-19 23:59:59 +0200')),              
    PARTITION p20120420 VALUES LESS THAN (to_days('2012-04-20 23:59:59 +0200')),               
    PARTITION p20120421 VALUES LESS THAN (to_days('2012-04-21 23:59:59 +0200')),             
    PARTITION p20120422 VALUES LESS THAN (to_days('2012-04-22 23:59:59 +0200')),                  
    PARTITION p20120423 VALUES LESS THAN (to_days('2012-04-23 23:59:59 +0200')),                    
    PARTITION p20120424 VALUES LESS THAN (to_days('2012-04-24 23:59:59 +0200'))                
);

the code give me this error:

A PRIMARY KEY must included all columns in the table's partitioning function

i need that the table is partition only by created_at and it can't be unique.

How can i do?

casperOne
  • 73,706
  • 19
  • 184
  • 253
Dabidi
  • 397
  • 5
  • 14
  • Welcome to Stack Overflow. You can format source code blocks with the `{}` toolbar button (or adding a 4-char indentation to all lines). I've done it for you this time. – Álvaro González Apr 26 '12 at 10:36

1 Answers1

0

make your primary key (id, created_at) and since id is unique, this is guaranteed to be unique. you can then use created_at as your table partition criterion.

kharles
  • 326
  • 5
  • 14