PostgreSQL table for large? Not enough memory?

Question

I have a table with ~ 3.8 million rows. When I query the whole table, I get

ERROR: value overflows in number format

referring to the value returned by the user-defined function. But if I split the table by about half (see below), everything will be fine.

SELECT day,item,price,
    CAST(my_func(price) OVER (PARTITION BY item ORDER BY day) AS numeric(8,2)),
    FROM my_table
    --WHERE day < '3/1/2013';
    --WHERE day >= '3/1/2013';

The statement with the expression WHEREis executed without error.

The price numeric(8,2)in the price column is not more than numbers numeric(8,2). In any case, changing the format to numeric(20,2)did not matter.

Here is the table definition:

    CREATE TABLE my_table
    (
        item    character(5)    NOT NULL,
        day     date            NOT NULL,
        price   numeric(8,2),
        CONSTRAINT  my_table_pk PRIMARY KEY (item, day)
    );

... and function:

    CREATE OR REPLACE FUNCTION my_func2 (avg numeric, IN price numeric)
    RETURNS numeric AS $$
    DECLARE
        alpha numeric;
    BEGIN
        alpha := 2.0/51;
        RETURN
            CASE
                WHEN avg IS NULL THEN price  -- avg is NULL for the first row, so price is returned
                ELSE round((alpha * price + (1-alpha) * avg),2)
            END;
    END;
    $$ LANGUAGE PLPgSQL;

... used together:

    CREATE AGGREGATE my_func(numeric) (SFUNC = my_func2, STYPE = numeric);

Answer 1

. numeric(8,2) , , , my_func() , . , :

select 12.34::numeric(8,2);
 numeric 
---------
   12.34

select 12.345678::numeric(8,2);
 numeric 
---------
   12.35

select 12.3456789::numeric(8,2);
 numeric 
---------
   12.35

select 123456.123456789::numeric(8,2);
  numeric  
-----------
 123456.12

select 1234567.123456789::numeric(8,2);
ERROR:  numeric field overflow
DETAIL:  A field with precision 8, scale 2 must round to an absolute value less than 10^6.

select 1234567.8::numeric(8,2);
ERROR:  numeric field overflow
DETAIL:  A field with precision 8, scale 2 must round to an absolute value less than 10^6.

, 8 2 . , 8 . , , 1234567.123456789 1234567.12, 1234567.12 9 , 8. 1234567.8, 8. , 2 , postgres sohuld 1234567.80, , 9 8.

, :

• , my_func(), numeric(16,2) 16 ( ).
• , numeric real. : (my_func(price) OVER (PARTITION BY item ORDER BY day))::real
• , round(my_func(price) OVER (PARTITION BY item ORDER BY day), 2). my_func(), round(returned_value, 2).

/ , . , my_func(), 6 . , , :

WITH not_casted AS (
    SELECT day,item,price,
    my_func(price) OVER (PARTITION BY item ORDER BY day) AS fprice
    FROM my_table
)
SELECT * FROM not_casted
WHERE fprice > 999999.99

, , . , , numeric(8,2) my_func(), , . , .

UPDATE

, . :  - AGGREGATE,  - AGGREGATE , (). 10 , item 10 31 . , , , :)

:

-- typecast price and arithmetics to numeric(8,2)
CREATE OR REPLACE FUNCTION my_func_numeric_8_2_a (avg numeric(8,2), IN price numeric(8,2))
RETURNS numeric(8,2) AS $$
DECLARE
    alpha numeric;
BEGIN
    alpha := 2.0/51;
    RETURN
        CASE
            WHEN avg IS NULL THEN price
            ELSE (alpha * price + (1-alpha) * avg)::numeric(8,2)
        END;
    END;
$$ LANGUAGE PLPgSQL;
CREATE AGGREGATE my_func_numeric_8_2(numeric(8,2)) (SFUNC = my_func_numeric_8_2_a, STYPE = numeric(8,2));


-- typecast price and arithmetics to numeric and round(arithmetics, 2)
CREATE OR REPLACE FUNCTION my_func_numeric_round_a(avg numeric, IN price numeric)
RETURNS numeric AS $$
DECLARE
    alpha numeric;
BEGIN
    alpha := 2.0/51;
    RETURN
    CASE
        WHEN avg IS NULL THEN price
            ELSE round((alpha * price + (1-alpha) * avg), 2)
        END;
    END;
$$ LANGUAGE PLPgSQL;
CREATE AGGREGATE my_func_numeric_round(numeric) (SFUNC = my_func_numeric_round_a, STYPE = numeric);

-- no typecast (double precision type)
CREATE OR REPLACE FUNCTION my_func_dp_a(avg double precision, IN price double precision)
RETURNS double precision AS $$
DECLARE
    alpha double precision;
BEGIN
    alpha := 2.0/51;
    RETURN
    CASE
        WHEN avg IS NULL THEN price
            ELSE (alpha * price + (1-alpha) * avg)
        END;
    END;
$$ LANGUAGE PLPgSQL;
CREATE AGGREGATE my_func_dp(double precision) (SFUNC = my_func_dp_a, STYPE = double precision);

-- typecast price and arithmetics to numeric
CREATE OR REPLACE FUNCTION my_func_numeric_a(avg numeric, IN price numeric)
RETURNS numeric AS $$
DECLARE
    alpha numeric;
BEGIN
    alpha := 2.0/51;
    RETURN
    CASE
        WHEN avg IS NULL THEN price
            ELSE (alpha * price + (1-alpha) * avg)
        END;
    END;
$$ LANGUAGE PLPgSQL;
CREATE AGGREGATE my_func_numeric(numeric) (SFUNC = my_func_numeric_a, STYPE = numeric);

, :

WITH sample AS
(
    SELECT "day", (random())*10 AS price, generate_series(1,10)::text AS item
    FROM (SELECT generate_series('2000-01-01'::timestamp, '2000-01-31'::timestamp, '1 day'::interval)::date AS "day") AS calendar
)
SELECT  "day", item, price,
        -- typecast price and arithmetics to numeric(8,2)
        my_func_numeric_8_2(price::numeric(8,2))  OVER (PARTITION BY item ORDER BY "day") AS numeric_8_2,

        -- typecast price and arithmetics to numeric and round(arithmetics, 2)
        my_func_numeric_round(price::numeric)  OVER (PARTITION BY item ORDER BY "day") AS numeric_round,

        -- typecast price and arithmetics to numeric and round the final result
        round(my_func_numeric(price::numeric)  OVER (PARTITION BY item ORDER BY "day"), 2) AS round_numeric,

        -- no typecast (double precision type)
        my_func_dp(price)  OVER (PARTITION BY item ORDER BY "day") AS no_typecast,

        -- typecast price and arithmetics to numeric
        my_func_numeric(price::numeric)  OVER (PARTITION BY item ORDER BY "day") AS numeric
FROM sample
ORDER BY item, "day"

- random(). , , , price . , ( ): my_func_dp(price) , my_func_numeric_8_2(price::numeric(8,2)) , .

, , my_func_numeric(price::numeric) , numeric , , . pgAdmin, .