How to diagnose and fix the 22013 invalid_preceding_or_following_size error code in Postgres.

by

The 22013 error code in PostgreSQL, described as “invalid_preceding_or_following_size”, occurs when an invalid argument is used with the PRECEDING or FOLLOWING keywords in window functions. These keywords are used to define the window frame, which is the set of rows that are used for calculating the window function result for each row.

To diagnose and fix this error, you should:

  1. Check Frame Extent Specification: Ensure that the arguments provided to PRECEDING or FOLLOWING are non-negative integers when specifying the number of rows. If you’re using RANGE with PRECEDING or FOLLOWING, make sure that the argument is a non-negative numeric expression.
  2. Avoid Using Variables: You cannot use variables for the frame extent in PRECEDING or FOLLOWING. The frame extent must be a literal constant or an expression that evaluates to a constant during the query execution.
  3. Check for Logical Frames: When using RANGE with logical offsets (like CURRENT ROW), ensure that the sort key of the window is numeric because logical frames with PRECEDING or FOLLOWING are not allowed for non-numeric sort keys.

Here are some examples that demonstrate how to avoid the 22013 error:

  • Correct Use of PRECEDING and FOLLOWING:
  SELECT col,
         SUM(col) OVER (ORDER BY col ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM table_name;
  -- This will sum the current row's col value with the one preceding and following it.
  • Incorrect Use of PRECEDING:
  -- This will result in an error:
  -- SELECT col,
  --        SUM(col) OVER (ORDER BY col ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING)
  -- FROM table_name;

  -- Instead, you should use a non-negative integer:
  SELECT col,
         SUM(col) OVER (ORDER BY col ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM table_name;
  -- This will return the correct result without an error.
  • Avoiding Variables in PRECEDING/FOLLOWING:
  -- Assuming that variable 'x' is defined with a value of 1, this will result in an error:
  -- SELECT col,
  --        SUM(col) OVER (ORDER BY col ROWS BETWEEN x PRECEDING AND 1 FOLLOWING)
  -- FROM table_name;

  -- Instead, use a literal constant:
  SELECT col,
         SUM(col) OVER (ORDER BY col ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM table_name;
  • Correct Use of RANGE:
  SELECT col,
         SUM(col) OVER (ORDER BY col RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING)
  FROM table_name;
  -- This assumes 'col' is numeric and the window frame is defined with a logical range.
  • Incorrect Use of Logical Frames with Non-numeric Sort Key:
  -- This will result in an error if 'col' is not numeric:
  -- SELECT col,
  --        SUM(col) OVER (ORDER BY col RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING)
  -- FROM table_name;

  -- Instead, ensure that the sort key is numeric or use ROWS instead of RANGE:
  SELECT col,
         SUM(col) OVER (ORDER BY col ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
  FROM table_name;

For more information on window functions and framing in PostgreSQL, you can refer to the PostgreSQL documentation on window functions.

Leave a Comment