#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    1
    Rep Power
    0

    RENAME SUBPARTITIONS in postgresql


    I have a table with partition as follows:

    parent_schema | parent | child | child_schema
    ---------------+--------------------------+--------+-------------------------------
    public | log_mining_raw | public | log_mining_raw_reg0

    and subpartition as:
    parent_schema | parent | child | child_schema
    ---------------+-------------------------------+--------+-------------------------------------
    public | log_mining_raw_reg0 | public | log_mining_raw_reg0_date0
    public | log_mining_raw_reg0 | public | log_mining_raw_reg0_date1
    public | log_mining_raw_reg0 | public | log_mining_raw_reg0_date2
    public | log_mining_raw_reg0 | public | log_mining_raw_reg0_date3
    public | log_mining_raw_reg0 | public | log_mining_raw_reg0_date4
    (5 rows)

    I am running the following command but getting syntax error for this:

    ALTER TABLE log_mining_raw RENAME SUBPARTITION log_mining_raw_reg0_date2 TO test1;

    ERROR: syntax error at or near "log_mining_raw_reg0_date0"

    I also tried
    ALTER TABLE log_mining_raw_reg0 RENAME PARTITION log_mining_raw_reg0_date2 TO test1;
    Getting the same error.

    My intent is to either rename or move partition so that everyday log_mining_raw_reg0_date0 moves to log_mining_raw_reg0_date1, date1 to date2 ....

    Can you please let me know what I could be missing?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    What made you believe that syntax is valid for Postgres? There is nothing in the manual that would suggest that your attemp should work.

    There is no such thing as a partition or sub-partition in Postgres, each "partition" is a regular table. So just rename the table:
    Code:
    ALTER TABLE log_mining_raw_reg0_date2 RENAME  TO test1
    More details about the ALTER TABLE command can be found in the manual: http://www.postgresql.org/docs/current/static/sql-altertable.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    So you are trying to do logrotation for your data by renaming tables at regular intervals? That doesn't sounds particularly safe to me; if your renaming fails today, all your data ends up in different tables, your applications start using the wrong datam everything blows up.

    Personally I'd use tablenames based on the current date so the data for "2013_11_07" is in table "data_log_20131107". And it will still be in that table tomorrow and next week, and your application can be sure that the data will not suddenly disappear.

    Then, if you really need a number of logX entries that allways point to the last X days, you could create/alter views named logX and point them to the actual datenamed tables. Or just create one view that aggregates the data from the newest X tables.

IMN logo majestic logo threadwatch logo seochat tools logo