Unable to use named parameters with type specifiers in queries

[Stvad]

Say I have the query that has type specifier within it e.g.:

SELECT SUM(used) as used, (SUM(used)::FLOAT/SUM(capacity))*100 AS percent_used, SUM(capacity) as capacity FROM table WHERE something = :somethingId;

(Type specifier being ::FLOAT)
And I want to use the named parameter style.
If I execute the aforementioned query as is - I'm going to get an error because pg8000 is going to try to interpret the ::FLOAT as parameters specification and I don't provide the corresponding parameter in parameters dict. My question is - what is the proper way to work around this situation? Is there a way to escape ::FLOAT so pg8000 won't interpret it as a parameter name?

I know that I can solve this particular problem by multiplying one of the operands by 1.0, but it does not work for other type specification (e.g. ::decimal, ::text, etc).

[Stvad]

@tlocke I'd appreciate your comment on this one. Seems like a bug to me.

[tlocke]

Hi @Stvad, is it possible to use CAST(SUM(used) as FLOAT) rather than SUM(used)::FLOAT? Apparently this is preferred anyway because CAST is part of standard SQL, but :: is specific to Postgres.

https://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

[Stvad]

while that would probably work for my use-case, I think the pg8k should support :: given that it's a Postgre specific library. I even have a PR out to fix this ;)

[tlocke]

@Stvad I think you're right, so I've used your PR to fix this. Thanks!