Thread: casting BOOL to somthng
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 ?
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 <http://www.postgresql.org/docs/current/static/sql-createcast.html> Hope this helps! Michael Glaesemann grzm myrealbox com
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......
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
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
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;
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.
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.
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 ?!
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
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?
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/
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
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'.
> 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.
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.
> select 't'::text::bool; > ERROR: cannot cast type text to boolean > > If you're thinking 't'::bool, that's something different. Ok i have nothing to opppose and by the way (!!!) why TEXT can not be casted to REGCLASS ?
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
Peter Eisentraut <peter_e@gmx.net> writes:
> 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 | icharacter varying | i"char"
| iname | ibigint | ismallint | iinteger
| ioid | ireal | idouble precision | imacaddr
| ecidr | einet | edate | itime
withouttime zone | itimestamp without time zone | itimestamp with time zone | iinterval |
itimewith time zone | inumeric | 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
On Wed, 1 Sep 2004, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > 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.