PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 716 pages ISBN 0954612027 RRP £32.00 ($49.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 1 Buy the book here! >>> learn more

3.9.5 Caveats

The following caveats apply to partitioned tables:

• There is currently no way to verify that all of the CHECK constraints are mutually exclusive. Care is required by the database designer.
• There is currently no simple way to specify that rows must not be inserted into the master table. A CHECK (false) constraint on the master table would be inherited by all child tables, so that cannot be used for this purpose. One possibility is to set up an ON INSERT trigger on the master table that always raises an error. (Alternatively, such a trigger could be used to redirect the data into the proper child table, instead of using a set of rules as suggested above.)

The following caveats apply to constraint exclusion:

• Constraint exclusion only works when the query's WHERE clause contains constants. A parameterized query will not be optimized, since the planner cannot know what partitions the parameter value might select at run time. For the same reason, “stable” functions such as CURRENT_DATE must be avoided.
• Avoid cross-data type comparisons in the CHECK constraints, as the planner will currently fail to prove such conditions false. For example, the following constraint will work if x is an integer column, but not if x is a bigint:

  CHECK ( x = 1 )
  

  For a bigint column we must use a constraint like:

  CHECK ( x = 1::bigint )
  

  The problem is not limited to the bigint data type--it can occur whenever the default data type of the constant does not match the data type of the column to which it is being compared. Cross-data type comparisons in the supplied queries are usually OK, just not in the CHECK conditions.
• All constraints on all partitions of the master table are considered for constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably.
• Don't forget that you still need to run ANALYZE on each partition individually. A command like

  ANALYZE measurement;
  

  will only process the master table.