Thread: Syntax Error for "boolean('value')" Type Casting
PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 64-bit
The following intuitively valid statement fails:
SELECT boolean(‘true’)
>>SQL Error: ERROR: syntax error at or near "("
>>LINE 1: SELECT boolean('true')
>> ^
The following work as expected:
SELECT bool(‘true’)
SELECT ‘true’::boolean
SELECT ‘true’::bool
SELECT bool ‘true’
SELECT boolean ‘true’
SELECT CAST('true' AS boolean)
SELECT CAST('true' AS bool)
While I’m here…is there any downside to using the “type(value)” form of casting versus other forms? The main place I will be using them is in SQL Select statements stored in textual form with parameter placeholders. The software I use for development allows parameters in the form of “:paramname” as opposed to the limited “?” so using the “::type” cast form is difficult. The “CAST( value AS type)” is too verbose for my liking and the “type ‘true’” just looks wrong to my eyes – not a huge fan of function calls that do not use parenthesis.
A sample full query form would be:
SELECT * FROM table WHERE col1 = boolean(:value);
I can replace the “:value” with a “?” before passing it to JDBC (or with a hard-coded value within Java if so desired) and can use it as-is within PostgreSQL Maestro.
On 21 May 2011, at 21:17, David Johnston wrote:
> SELECT boolean('true')
>
>>> SQL Error: ERROR: syntax error at or near "("
>>> LINE 1: SELECT boolean('true')
>>> ^
>
> The following work as expected:
>
> SELECT bool('true')
(...)
> While I'm here.is there any downside to using the "type(value)" form of
> casting versus other forms?
You're comparing apples and oranges here, you're not using a cast at all in fact.
Instead, you're calling the transformation function that's used by the cast. It just happens to be called the same as
thetype in some cases, which is why boolean(value) doesn't work.
Now calling such functions will probably work just the same as the cast in most cases, but there will be some
discrepancies,as follows from reading: http://www.postgresql.org/docs/9.0/interactive/sql-createcast.html
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4dd8e3fe11921124321257!
On May 22, 2011, at 6:22, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:
> On 21 May 2011, at 21:17, David Johnston wrote:
>
>> SELECT boolean('true')
>>
>>>> SQL Error: ERROR: syntax error at or near "("
>>>> LINE 1: SELECT boolean('true')
>>>> ^
>>
>> The following work as expected:
>>
>> SELECT bool('true')
>
> (...)
>
>> While I'm here.is there any downside to using the "type(value)" form of
>> casting versus other forms?
>
> You're comparing apples and oranges here, you're not using a cast at all in fact.
>
> Instead, you're calling the transformation function that's used by the cast. It just happens to be called the same as
thetype in some cases, which is why boolean(value) doesn't work.
>
> Now calling such functions will probably work just the same as the cast in most cases, but there will be some
discrepancies,as follows from reading: http://www.postgresql.org/docs/9.0/interactive/sql-createcast.html
>
> Alban Hertroys
>
>
I did end up finding the notes on the how's and caveats of using the direct method version for casting. I also tried
"boolean"and indeed got the "function not found" error. All that said, it seems that one could add the Boolean
function,even if it just calls bool, to get consistent behavior.
Doesn't matter to me at this point since I've decided to use the SQL conforming syntax of CAST(v as type).
Thank you the response though
David J.