Invalid input for integer on VIEW

[mike]

I have the following view definition

Column | Type | Modifiers
----------------+-----------------------+-----------
bcode | character varying(15) |
subhead | text |
sc_description | character varying(60) |
Budget | numeric |
expenditure | numeric |
balance | numeric |
head | integer |
period | integer |
View definition:
SELECT
CASE
WHEN vw_rec_sum.code IS NULL AND vw_pay_sum.sum IS NOT NULL
THEN vw_pay_sum.code
ELSE vw_rec_sum.code
END AS bcode,
CASE
WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 'Salary
Costs'::text
WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 'Startup
Costs'::text
WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 'Running
Costs'::text
WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 'Training
Costs'::text
ELSE NULL::text
END AS subhead, sc_description, vw_rec_sum.sum AS "Budget",
vw_pay_sum.sum AS expenditure,
CASE
WHEN vw_pay_sum.sum IS NULL THEN vw_rec_sum.sum
WHEN vw_pay_sum.sum < 0.01 THEN 0.00 - vw_pay_sum.sum +
vw_rec_sum.sum
ELSE vw_rec_sum.sum - vw_pay_sum.sum
END AS balance,
CASE
WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 1
WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 2
WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 3
WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 4
ELSE NULL::integer
END AS head,
CASE
WHEN to_number(vw_rec_sum.code::text, '999'::text) >
194::numeric THEN 3
WHEN to_number(vw_rec_sum.code::text, '999'::text) <
195::numeric AND to_number(vw_rec_sum.code::text, '999'::text) >
50::numeric THEN 2
ELSE 1
END AS period
FROM vw_rec_sum
FULL JOIN vw_pay_sum ON vw_rec_sum.code::text = vw_pay_sum.code::text
JOIN vw_ac ON vw_rec_sum.code::text = vw_ac.id::text
ORDER BY to_number(vw_rec_sum.code::text, '999'::text);
However whenever I try to query it with criteria on the period column I
get SELECT * FROM vw_budget HAVING "period"='3';
ERROR: invalid input syntax for type numeric: " "

If I try on the head column the query runs

Getting stumped - anyone any idea what is going on here.

This is with 7.4.3

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[mike]

On Tue, 2004-08-24 at 15:42 +0100, mike wrote:

I have the following view definition

Column | Type | Modifiers
----------------+-----------------------+-----------
bcode | character varying(15) |
subhead | text |
sc_description | character varying(60) |
Budget | numeric |
expenditure | numeric |
balance | numeric |
head | integer |
period | integer |
View definition:
SELECT
CASE
WHEN vw_rec_sum.code IS NULL AND vw_pay_sum.sum IS NOT NULL
THEN vw_pay_sum.code
ELSE vw_rec_sum.code
END AS bcode,
CASE
WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 'Salary
Costs'::text
WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 'Startup
Costs'::text
WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 'Running
Costs'::text
WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 'Training
Costs'::text
ELSE NULL::text
END AS subhead, sc_description, vw_rec_sum.sum AS "Budget",
vw_pay_sum.sum AS expenditure,
CASE
WHEN vw_pay_sum.sum IS NULL THEN vw_rec_sum.sum
WHEN vw_pay_sum.sum < 0.01 THEN 0.00 - vw_pay_sum.sum +
vw_rec_sum.sum
ELSE vw_rec_sum.sum - vw_pay_sum.sum
END AS balance,
CASE
WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 1
WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 2
WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 3
WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 4
ELSE NULL::integer
END AS head,
CASE
WHEN to_number(vw_rec_sum.code::text, '999'::text) >
194::numeric THEN 3
WHEN to_number(vw_rec_sum.code::text, '999'::text) <
195::numeric AND to_number(vw_rec_sum.code::text, '999'::text) >
50::numeric THEN 2
ELSE 1
END AS period
FROM vw_rec_sum
FULL JOIN vw_pay_sum ON vw_rec_sum.code::text = vw_pay_sum.code::text
JOIN vw_ac ON vw_rec_sum.code::text = vw_ac.id::text
ORDER BY to_number(vw_rec_sum.code::text, '999'::text);
However whenever I try to query it with criteria on the period column I
get SELECT * FROM vw_budget HAVING "period"='3';
ERROR: invalid input syntax for type numeric: " "

If I try on the head column the query runs

Getting stumped - anyone any idea what is going on here.

This is with 7.4.3

If I do the same query, except to create a new table, everything works,
so is this a view bug?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

mike <mi**@bristolreccc.co.uk> writes:

If I do the same query, except to create a new table, everything works,
so is this a view bug?

Possibly, but you haven't given enough info to let someone else
reproduce the problem. A SQL script that creates all the necessary
tables and the view and then triggers the failure would make it much
easier for us to investigate.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

mike <mi**@bristolreccc.co.uk> writes:

On Tue, 2004-08-24 at 12:30 -0400, Tom Lane wrote:

Possibly, but you haven't given enough info to let someone else
reproduce the problem.

Is this OK, or do you want some data as well?

I plugged in the view definition from your original mail and got

regression=# SELECT * FROM vw_budget HAVING "period"='3';
bcode | subhead | sc_description | Budget | expenditure | balance | head | period
-------+---------+----------------+--------+-------------+---------+------+--------
(0 rows)

So either the problem requires data, or there is something wrong with
your left() function, which was not included in the script. I guessed
at

create function left(text, integer) returns text as
'select substr($1,$2)' language sql ;

but I dunno if this is anything like what you are using.

Please actually *test* the script you are submitting, next time, and
verify that it reproduces the problem starting from an empty database.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match