hello
why BOOL can not be casted to TEXT
....nevertheless BOOL has a textual (output) representation 't' and 'f' letters
why not to use this fact to define cast to TEXT ?
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
On Aug 31, 2004, at 6:06 PM, sad wrote:
> why BOOL can not be casted to TEXT
> ....nevertheless BOOL has a textual (output) representation 't' and
> 'f' letters
> why not to use this fact to define cast to TEXT ?
I'm not sure of the reason why there isn't a built-in cast from boolean
to text, though I'm not a big fan of casts in general so it doesn't
really bother me too much. If this is something you desire, I believe
you can use CREATE CAST to make your own cast from boolean to text.
test=# select true::text;
ERROR: cannot cast type boolean to text
create or replace function bool_to_text (boolean)
returns text
strict
language sql as '
select case
when $1 then \'t\'
else \'f\'
end;
';
create cast (boolean as text)
with function bool_to_text(boolean)
as assignment;
test=# select true::text;
text
------
t
(1 row)
You can find more information at
Hope this helps!
Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
you wrote:
> you can use CREATE CAST to make your own cast from boolean to text.
thnx it helps.
and i am still desire to know _WHY_ there are no predefined cast for BOOL ?
and at the same time there are predefined casts for INT and FLOAT......
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
sad wrote:
> you wrote:
>
>
>>you can use CREATE CAST to make your own cast from boolean to text.
>
>
> thnx it helps.
>
> and i am still desire to know _WHY_ there are no predefined cast for BOOL ?
> and at the same time there are predefined casts for INT and FLOAT......
I'd like to understand in what context you would find this useful.
Don't take me wrong please. I'm by no means a db expert, but I can't
see a purpose for such a cast. Can you provide a reasonable example of
such usage?
Thanks.
--
Until later, Geoffrey Registered Linux User #108567
AT&T Certified UNIX System Programmer - 1995
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Aug 31, 2004, at 8:24 PM, sad wrote:
> and i am still desire to know _WHY_ there are no predefined cast for
> BOOL ?
> and at the same time there are predefined casts for INT and FLOAT......
I think the main reason is what is the proper textual representation of
BOOLEAN? True, PostgreSQL returns 't' as a representation for the
BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
'true' or other representations. Picking one is perhaps arbitrary.
In a similar vein, some people would like to cast BOOLEAN to INTEGER,
as often BOOLEAN TRUE and BOOLEAN FALSE are represented as INTEGER 1
and INTEGER 0, respectively, in some systems. However, other systems
use different INTEGER representations such as INTEGER 1 and INTEGER -1
for BOOLEAN TRUE and FALSE, respectively. Again, the choice of how to
cast BOOLEAN to INTEGER is kind of arbitrary.
Luckily PostgreSQL provides convenient ways of making user-defined
casts.
Just my thoughts.
Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Tuesday 31 August 2004 16:22, Geoffrey wrote:
> sad wrote:
> > you wrote:
> >>you can use CREATE CAST to make your own cast from boolean to text.
> >
> > thnx it helps.
> >
> > and i am still desire to know _WHY_ there are no predefined cast for BOOL
> > ? and at the same time there are predefined casts for INT and FLOAT......
>
> I'd like to understand in what context you would find this useful.
> Don't take me wrong please. I'm by no means a db expert, but I can't
> see a purpose for such a cast. Can you provide a reasonable example of
> such usage?
Yes i can.
look:
CREATE TABLE t (a int, b text, c bool);
SELECT 'the row is: a='||a::TEXT||' b='||b||' c='||c::TEXT FROM t;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote:
> On Aug 31, 2004, at 8:24 PM, sad wrote:
> > and i am still desire to know _WHY_ there are no predefined cast for
> > BOOL ?
> > and at the same time there are predefined casts for INT and FLOAT......
>
> I think the main reason is what is the proper textual representation of
> BOOLEAN? True, PostgreSQL returns 't' as a representation for the
> BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
> 'true' or other representations. Picking one is perhaps arbitrary.
There are many (infinite number) of INT representations,
"Picking one is perhaps arbitrary." But you poke one and using it.
If some one wants another representation you ask him do define his own
function and use it instead of cast. And you are right. In your system
integers textully represented as you define. Just define one representation
for boolean and leave the rest for user definition.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On Wed, 1 Sep 2004, sad wrote:
> On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote:
> > On Aug 31, 2004, at 8:24 PM, sad wrote:
> > > and i am still desire to know _WHY_ there are no predefined cast for
> > > BOOL ?
> > > and at the same time there are predefined casts for INT and FLOAT......
> >
> > I think the main reason is what is the proper textual representation of
> > BOOLEAN? True, PostgreSQL returns 't' as a representation for the
> > BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
> > 'true' or other representations. Picking one is perhaps arbitrary.
>
> There are many (infinite number) of INT representations,
> "Picking one is perhaps arbitrary." But you poke one and using it.
There's a fairly accepted convention for integer representations.
There's no such convention for boolean representations.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
> On Wed, 1 Sep 2004, sad wrote:
> > On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote:
> > > On Aug 31, 2004, at 8:24 PM, sad wrote:
> > > > and i am still desire to know _WHY_ there are no predefined cast for
> > > > BOOL ?
> > > > and at the same time there are predefined casts for INT and
> > > > FLOAT......
> > >
> > > I think the main reason is what is the proper textual representation of
> > > BOOLEAN? True, PostgreSQL returns 't' as a representation for the
> > > BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
> > > 'true' or other representations. Picking one is perhaps arbitrary.
> >
> > There are many (infinite number) of INT representations,
> > "Picking one is perhaps arbitrary." But you poke one and using it.
>
> There's a fairly accepted convention for integer representations.
> There's no such convention for boolean representations.
then why do you print its value on a screen ?!
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote:
> On Sep 1, 2004, at 2:41 PM, sad wrote:
> > On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
> >> There's a fairly accepted convention for integer representations.
> >> There's no such convention for boolean representations.
> >
> > then why do you print its value on a screen ?!
>
> Perhaps because if you don't print *something* you can't see it?
since you printed it you poke a convention (of casting to string)
if you can print it on screen why not to print it in string?
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
> There's a difference between an output function and a cast to text.
> One gives you an external representation of the data for end use. The
> other gives you an internal representation for manipulation.
And at the same time
't'::TEXT can be casted to BOOL
't'::BOOL
but reverse.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On Sep 1, 2004, at 2:41 PM, sad wrote:
> On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
>>
>> There's a fairly accepted convention for integer representations.
>> There's no such convention for boolean representations.
>
> then why do you print its value on a screen ?!
Perhaps because if you don't print *something* you can't see it?
Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
sad wrote:
> since you printed it you poke a convention (of casting to string)
>
> if you can print it on screen why not to print it in string?
Allow me an attempt at a philosophical explanation:
The external representation to the API is arbitrary, because it's part
of the API specification, and it varies. If you use libpq, you get a
character 't' or 'f', if you use ECPG you get a C bool (int) datum, if
you use JDBC, you get a Java bool value, etc. psql uses libpq, so you
see 't' or 'f'. MS Access maybe uses ODBC and you might see a checkbox
or something. It's part of the interface definition.
The cast to text, however, is part of the data model, and it has to be
both natural and universal. I think you agree that there is no
universal, obvious correspondence between character strings and boolean
values, at least not nearly as universal and obvious as the well-known
correspondence between character strings and numbers. We could pick
one arbitrary correspondence and implement it, and if we did we would
probably pick one that is consistent with the mapping used by libpq and
other frontends. But doing that gains no functionality, so why bother?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Sep 1, 2004, at 2:55 PM, sad wrote:
> On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote:
>> On Sep 1, 2004, at 2:41 PM, sad wrote:
>>> On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
>>>> There's a fairly accepted convention for integer representations.
>>>> There's no such convention for boolean representations.
>>>
>>> then why do you print its value on a screen ?!
>>
>> Perhaps because if you don't print *something* you can't see it?
>
> since you printed it you poke a convention (of casting to string)
OT: 'pick' or 'choose'. 'Poke' means something else entirely.
> if you can print it on screen why not to print it in string?
I can see your point, but I think that representing the value and
casting the value to another type are two different things. Given, as
Stephen pointed out, that there is no standard convention for
representing BOOLEAN values, whatever the choice is is not going to
satisfy nearly anyone. For me, I would *not* want TRUE to be
represented as 't', nor would I want to have to set up a separate cast
(or formating function, or CASE statement) to make it print 'TRUE'.
Others, I'm sure, would rather see it as 'true' (lowercase). Why should
they be penalized to suit me?
I've seen very few people ask for a cast from BOOLEAN to TEXT. Given
the apparently limited number of people who desire it, and the various
ways BOOLEAN may be represented as text, I think it's much better to
leave it up to the individual user to define their own cast to do so,
and PostgreSQL provides an easy method to do so.
Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Wed, 1 Sep 2004, sad wrote:
> On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
> > On Wed, 1 Sep 2004, sad wrote:
> > > On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote:
> > > > On Aug 31, 2004, at 8:24 PM, sad wrote:
> > > > > and i am still desire to know _WHY_ there are no predefined cast for
> > > > > BOOL ?
> > > > > and at the same time there are predefined casts for INT and
> > > > > FLOAT......
> > > >
> > > > I think the main reason is what is the proper textual representation of
> > > > BOOLEAN? True, PostgreSQL returns 't' as a representation for the
> > > > BOOLEAN value TRUE, but some people might want it to return 'TRUE' or
> > > > 'true' or other representations. Picking one is perhaps arbitrary.
> > >
> > > There are many (infinite number) of INT representations,
> > > "Picking one is perhaps arbitrary." But you poke one and using it.
> >
> > There's a fairly accepted convention for integer representations.
> > There's no such convention for boolean representations.
>
> then why do you print its value on a screen ?!
There's a difference between an output function and a cast to text.
One gives you an external representation of the data for end use. The
other gives you an internal representation for manipulation.
Just because true is displayed in psql as 't' and false is displayed in
psql as 'f' does not mean that an expression like (true::text ||
false::text) has any conventional meaning let alone 'tf'.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Wed, 1 Sep 2004, sad wrote:
> > There's a difference between an output function and a cast to text.
> > One gives you an external representation of the data for end use. The
> > other gives you an internal representation for manipulation.
>
> And at the same time
>
> 't'::TEXT can be casted to BOOL
> 't'::BOOL
>
> but reverse.
select 't'::text::bool;
ERROR: cannot cast type text to boolean
If you're thinking 't'::bool, that's something different.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
sad wrote:
> On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote:
>
>>On Sep 1, 2004, at 2:41 PM, sad wrote:
>>
>>>On Wednesday 01 September 2004 09:24, Stephan Szabo wrote:
>>>
>>>>There's a fairly accepted convention for integer representations.
>>>>There's no such convention for boolean representations.
>>>
>>>then why do you print its value on a screen ?!
>>
>>Perhaps because if you don't print *something* you can't see it?
>
>
> since you printed it you poke a convention (of casting to string)
>
> if you can print it on screen why not to print it in string?
Simply for the sake of being able to recognize the value. If it doesn't
have some value printed, how do you know what the value is? Although
your example would work (from a previous post), I don't see a real world
use for such an effort.
There are work arounds that are quite simple.
--
Until later, Geoffrey Registered Linux User #108567
AT&T Certified UNIX System Programmer - 1995
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Peter Eisentraut
> The cast to text, however, is part of the data model, and it has to be
> both natural and universal. I think you agree that there is no
> universal, obvious correspondence between character strings and boolean
> values, at least not nearly as universal and obvious as the well-known
> correspondence between character strings and numbers. We could pick
> one arbitrary correspondence and implement it, and if we did we would
> probably pick one that is consistent with the mapping used by libpq and
> other frontends. But doing that gains no functionality, so why bother?
Actually it does gain functionality, because there are plenty of times
when you need to manipulate a textual representation of a data value.
We have casts to text for many datatypes already:
castsource | castcontext
-----------------------------+-------------
character | i
character varying | i
"char" | i
name | i
bigint | i
smallint | i
integer | i
oid | i
real | i
double precision | i
macaddr | e
cidr | e
inet | e
date | i
time without time zone | i
timestamp without time zone | i
timestamp with time zone | i
interval | i
time with time zone | i
numeric | i
(20 rows)
and I think it is reasonable to say that we should have them for all
types. My only beef with the above table is that most of these casts
should not be implicitly invokable --- I think you should have to write
an explicit CAST.
As for the "which representation" argument, both consistency and
implementation simplicity say that it should be whatever the datatype's
output function delivers. Indeed it's just a historical accident that
Postgres didn't define the datatype output functions as returning "text"
values in the first place.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On Wed, 1 Sep 2004, Tom Lane wrote:
> Peter Eisentraut
> > The cast to text, however, is part of the data model, and it has to be
> > both natural and universal. I think you agree that there is no
> > universal, obvious correspondence between character strings and boolean
> > values, at least not nearly as universal and obvious as the well-known
> > correspondence between character strings and numbers. We could pick
> > one arbitrary correspondence and implement it, and if we did we would
> > probably pick one that is consistent with the mapping used by libpq and
> > other frontends. But doing that gains no functionality, so why bother?
>
> Actually it does gain functionality, because there are plenty of times
> when you need to manipulate a textual representation of a data value.
I don't think that doing so apart from trying to do output representation
is really a good idea in general (and for that I'd think something like
to_char would be more appropriate). For example, if people are casting
macaddrs to text in order to substring them or some such, that implies
to me that there are additional operations on macaddr we should have.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html