4

Context: I'm trying to INSERT data in a partitioned table. My table is partitioned by months, because I have lots of data (and a volume expected to increase) and the most recent data is more often queried. Any comment on the partition choice is welcome (but an answer to my question would be more than welcome).

The documentation has a partition example in which, when inserting a line, a trigger is called that checks the new data date and insert it accordingly in the right "child" table. It uses a sequence of IF and ELSIF statements, one for each month. The guy (or gal) maintaining this has to create a new table and update the trigger function every month.

I don't really like this solution. I want to code something that will work perfectly and that I won't need to update every now and then and that will outlive me and my grand-grand-children.

So I was wondering if I could make a trigger that would look like this:

INSERT INTO get_the_appropriate_table_name(NEW.date) VALUES (NEW.*);

Unfortunately all my attempts have failed. I tried using "regclass" stuffs but with no success.

In short, I want to make up a string and use it as a table name. Is that possible?

Arthur
  • 1,974
  • 2
  • 21
  • 28
  • +1 for not liking that solution. I can imagine baby sitting something like this every month! – Icarus Sep 15 '11 at 16:01
  • You could make the partitions 'rolling' (current, month - 1, ... month - n), then inserts just have to check the offset from current (you don't need to change the insert queries every month, because names stay the same). Of course, you'd have to drop the last partition, and rename the others, and create a new one - but you'd be doing that anyways (drop/create). – Clockwork-Muse Sep 15 '11 at 16:39
  • @X-Zero That's a good idea. However if I insert "old" data I would still have to write all the if-elses. To be honest, that shouldn't happen. Also, I'm not planning to drop any data at all, so I'd have to create lots of tables in anticipation. So that wouldn't be perfect, but it's my best solution for now. Thanks! – Arthur Sep 15 '11 at 17:02
  • @Arthur - Inserting old data only needs the if-elses based on the offset from the current (not the absolutes like the example has). Be aware that some systems impose a limit on the number of partitions allowed. Keep in mind that in general (at all, I think), SQL does not allow identifiers (table/column names, etc) to be gathered from tables, and probably as returns from SPs; you need dynamic SQL instead. – Clockwork-Muse Sep 15 '11 at 18:30
  • Why don't you pre-create the tables for the next 2 years or so? Or create a cron job that creates the tables automatically. There is no reason to do that manually. –  Sep 15 '11 at 20:44

1 Answers1

1

I was just about to write a trigger function using EXECUTE to insert into a table according to the date_parts of now(), or create it first if it should not exist .. when I found that somebody had already done that for us - right under the chapter of the docs you are referring to yourself:
http://www.postgresql.org/docs/9.0/interactive/ddl-partitioning.html

Scroll all the way down to user "User Comments". Laban Mwangi posted an example.

Update:

The /interactive branch of the Postgres manual has since been removed, links are redirected. So the comment I was referring to is gone. Look to these later, closely related answers for detailed instructions:

For partitioning, there are better solutions by now, like range partitioning in Postgres 10 or later. Example:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • aaaaaaaaaah so much for reading the doc in ...French. I'll try that, it really seems to be it. Thank you! – Arthur Sep 16 '11 at 01:22
  • 1
    Oh wow. I posted that ages ago. I forgot all about it! Good to know that it's helping folks out there :D – Lmwangi May 14 '14 at 19:48