We have some big tables in our database and the boss thought that partitioning would be a good improvement to our system. I was charged to investigate it and develop all the needed scripts in a test db and explain everything to the rest later. I'm no dba, I'm a perl developer.
Focused on just one master table, I've produced a function to create a new children table, to create children tables for the existing information and to move things from master to children by batches. I don't think it's relevant, but our checks are on the year and month of the created field.
Now I'm building the insert trigger in the master database that would detect the period for the new record, look for the corresponding children table, creates it if missing, and inserts. Everything is ready but the insert is not working!
Our inserts statements are usually partial: will add the basic fields, do some things and fill in the rest (the fill in part will come later). So the insert statement is something like
INSERT INTO public.transactions ( created, tr_status )
VALUES (now(), 'created');
and leave Postgres to fill in the id. But in between
tr_status, the schema has a field called modified. And, when the data gets to the trigger function, this field is empty. Trying to insert the row will throw an error:
'INSERT INTO ' || child || ' VALUES ' || NEW.*
LINE 1: ...15_07 VALUES (123456,"2015-07-03 16:48:17.890627",,completed... ^
How can I use in the trigger the same fields that were in the insert statement?