This fails with ERROR: invalid input syntax for integer: "JOHN":
select * from (
select * from (VALUES ('100'), ('JOHN')) as A (n)
where trim(trim(n), '0123456789') = ''
) as B where n::integer <> -1;
But without the nested trim, this works just fine (although it would not
suffice for my purpose):
select * from (
select * from (VALUES ('100'), ('JOHN')) as A (n)
where trim(n, '0123456789') = ''
) as B where n::integer <> -1;
I think they should both work because the documentation says "A
sub-SELECT can appear in the FROM clause. This acts as though its
output were created as a temporary table for the duration of this
single SELECT command."
The cause is clear when you look at the error-ing EXPLAIN:
Values Scan on "*VALUES*" (cost=0.00..0.06 rows=1 width=32)
Filter: (((column1)::integer <> (-1)) AND (btrim(btrim(column1),
'0123456789'::text) = ''::text))
Versus the single-trim EXPLAIN:
Values Scan on "*VALUES*" (cost=0.00..0.05 rows=1 width=32)
Filter: ((btrim(column1, '0123456789'::text) = ''::text) AND
((column1)::integer <> (-1)))
The extra trim causes the cast-and-compare to happen before the
trim-and-compare. By my understanding PostgreSQL should not be allowed
to reorder the clause of the subselect before the outer select.
I'm running the Ubuntu postgresql package version 8.3.3-0ubuntu0.8.04.
I'm also interested in responses of the form "why not just do X?".
--
John Keith Hohm
<[EMAIL PROTECTED]>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
