The following bug has been logged on the website:
Bug reference: 17137
Logged by: Alex Zhuravlev
Email address: alexone07@mail.ru
PostgreSQL version: 13.3
Operating system: Red Hat 8.4.1-1, 64-bit
Description:
SQL expression doesn't execute with error:
SQL Error [42804]: ERROR: recursive query "t" column 1 has type character
varying(20) in non-recursive term but type character varying overall
Help: Cast the output of the non-recursive term to the correct type.
*But it works with text datatype.
Some repro's:
First:
WITH RECURSIVE t AS (
SELECT a::varchar AS b
FROM (
SELECT 'a'::varchar(20) AS a
) AS x
UNION ALL
SELECT 'a'::varchar AS b
FROM t
)
SELECT *
FROM t;
The Second:
CREATE TABLE tm1(a varchar(20)); SELECT a, a::varchar FROM tm1 \gdesc
The third:
CREATE TABLE t(n) AS SELECT '5.5'::numeric(20, 2); SELECT n::numeric FROM t
\gdesc
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c3bfe57c351cc783399fad539d5690d0
