BUG #7926: column 2 has type numeric(20, 2) in non-recursive term but type numeric overall

From: jakubkania(at)onet(dot)pl
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7926: column 2 has type numeric(20, 2) in non-recursive term but type numeric overall
Date: 2013-03-11 00:06:58
Message-ID: E1UEqGY-0000Qp-Po@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 7926
Logged by: Jakub Kania
Email address: jakubkania(at)onet(dot)pl
PostgreSQL version: 9.1.4
Operating system: Windows 7
Description:

I'm not really sure what is going on but I was advised on the IRC chanell to
report it.
I get the error:
ERROR: recursive query "bank" column 2 has type numeric(20,2) in
non-recursive term but type numeric overall
LINE 6: SELECT ARRAY[id],amount::numeric(20,2) FROM bank_transacti...
^
HINT: Cast the output of the non-recursive term to the correct type.

********** Error **********

ERROR: recursive query "bank" column 2 has type numeric(20,2) in
non-recursive term but type numeric overall
SQL state: 42804
Hint: Cast the output of the non-recursive term to the correct type.
Character: 140

WITH RECURSIVE bank(id,amount) AS (
SELECT ARRAY[id],amount::numeric(20,2) FROM bank_transactions
UNION ALL
SELECT b1.id||ARRAY[borg.id],b1.amount+borg.amount
FROM bank AS b1 LEFT JOIN
bank_transactions AS borg ON borg.id>ALL(b1.id)
)
SELECT * from bank LIMIT 100;

With schema:
CREATE TABLE bank_transactions
(
id serial NOT NULL,
amount numeric(20,2),
CONSTRAINT bank_transactions_pkey PRIMARY KEY (id )
)

Casting the column the error is pointing too doesn't help at all, casting
the one in the other select fixes it.
http://sqlfiddle.com/#!12/0d00c/1
http://pastebin.com/1zWYxSZZ

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2013-03-11 01:22:40 Re: New kind of crash?
Previous Message alealequi 2013-03-10 23:33:34 BUG #7925: doesnt works