Re: [SQL] casting BOOL to somthng
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] casting BOOL to somthng
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
Re: [SQL] casting BOOL to somthng
> 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
Re: [SQL] casting BOOL to somthng
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
[SQL] casting UNKNOWN to REGCLASS
> 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 ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] casting BOOL to somthng
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
[SQL] German "umlaut insensitive" query
I want to query words with German "umlauts" (special characters) with and without normalization. I want to find "grün" (green) written "gruen" as well. Using "LIKE" with locale de_DE.iso88591 or .utf-8 does not help (Locale support should affect "LIKE", http://www.postgresql.org/docs/7.3/static/charset.html#AEN21761). Any Idea how to solve this? Define a special Operator? Has anyone already done this before? I am using PostgreSQL 7.3.2 on Linux. TIA, Robert Strötgen. :) -- Robert Strötgen mailto:[EMAIL PROTECTED] http://www.stroetgen.de/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] casting BOOL to somthng
Peter Eisentraut <[EMAIL PROTECTED]> 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 | 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
Re: [SQL] casting BOOL to somthng
On Wed, 1 Sep 2004, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] German "umlaut insensitive" query
One solution: select replace( replace( replace( replace( 'Test ä ö ü ß', 'ä','ae'), 'ö','oe' ), 'ü','ue'), 'ß','ss' ); replace -- Test ae oe ue ss If you also have upcase-characters, you have to extend the statement. Robert Strötgen schrieb: I want to query words with German "umlauts" (special characters) with and without normalization. I want to find "grün" (green) written "gruen" as well. Using "LIKE" with locale de_DE.iso88591 or .utf-8 does not help (Locale support should affect "LIKE", http://www.postgresql.org/docs/7.3/static/charset.html#AEN21761). Any Idea how to solve this? Define a special Operator? Has anyone already done this before? I am using PostgreSQL 7.3.2 on Linux. TIA, Robert Strötgen. :) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Extracting fieldnames from a TABLE
Hi community,
I would like to retrieve all the fieldnames of a given table. In the
perl module Tie::DBI[1] i found the following fragment:
$dbh->prepare("LISTFIELDS $table");
in the case the DB supports this (Tie::DBI thinks so for Pg) or the
alternative is:
$dbh->prepare("SELECT * FROM $table WHERE 0=1");
The first one doesn't work in my PostgreSQL 7.4.3:
% LISTFIELDS foobar;
ERROR: syntax error at or near "LISTFIELDS" at character 1
%
and the seconds one looks ugly. Is there a solution for the problem?
Greetings
[1]http://search.cpan.org/~lds/Tie-DBI-0.93/lib/Tie/DBI.pm
--
So long... Fuzz
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] German "umlaut insensitive" query
select replace( replace( replace( replace( 'Test ä ö ü ß', 'ä','ae'), 'ö','oe' ), 'ü','ue'), 'ß','ss' ); Thanks a lot. A wrote this into a user defined function with lower() around the source string, and it works. :-) CREATE OR REPLACE FUNCTION public.unumlaut(varchar) RETURNS varchar AS 'select replace( replace( replace( replace( lower($1), \'ä\',\'ae\'), \'ö\',\'oe\' ), \'ü\',\'ue\'), \'ß\',\'ss\' );' LANGUAGE 'sql' IMMUTABLE STRICT; Best regards, Robert. :) -- Robert Strötgen mailto:[EMAIL PROTECTED] http://www.stroetgen.de/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] colored PL with emacs
On Aug 31, 2004, at 1:23 PM, Manuel Sugawara wrote: I have SQL highlighting, but what I want are colors for the PL/pgSQL key words. It would make PL programming much easier. Since the Pl/PgSQL code is quoted (x)emacs paints the whole thing using the string face. Delete one of the apostrophes delimiting the code while editing and you should get some highlighting. I'm working on an editor specifically designed for PostgreSQL and plpgsql programming. It colors syntax within function strings and helps get the quoting right (8.0 dollar quoting is also supported). The editor is based on emacs but it also has emulation modes that are more friendly for those who are not familiar with emacs. I hope to start beta testing the Mac OS X version next week, followed by a Windows version probably in October. I have not yet decided if I will do a Linux version, but it should be possible. The estimated price for this product is $65. Send me an email if you are interested in beta testing the Mac OS X version. Best, John DeSoi, Ph.D. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Extracting fieldnames from a TABLE
One way to do this is to use the column_info database handle method.
Here's a little perl script that accepts a table name as an argument
and returns the column names:
#!/usr/bin/perl
use DBI;
use strict;
my( $database, $table ) = @ARGV;
my $dbh = DBI->connect( "dbi:Pg:dbname=$database", 'postgres' );
my $sth = $dbh->column_info( undef, 'public', $table, '%' );
$sth->execute;
while( my @row = $sth->fetchrow_array ) {
print join( ' ', $row[ 3 ] ), "\n";
}
$sth->finish;
$dbh->disconnect;
This could be easily modified to stick the contents of $row[ 3 ] into
an array. You'd have to modify the user and schema as appropriate for
your database.
The fourth parameter to column_info is a wildcard so you get everything.
-tfo
On Sep 1, 2004, at 10:14 AM, Erik Wasser wrote:
Hi community,
I would like to retrieve all the fieldnames of a given table. In the
perl module Tie::DBI[1] i found the following fragment:
$dbh->prepare("LISTFIELDS $table");
in the case the DB supports this (Tie::DBI thinks so for Pg) or the
alternative is:
$dbh->prepare("SELECT * FROM $table WHERE 0=1");
The first one doesn't work in my PostgreSQL 7.4.3:
% LISTFIELDS foobar;
ERROR: syntax error at or near "LISTFIELDS" at character 1
%
and the seconds one looks ugly. Is there a solution for the problem?
Greetings
[1]http://search.cpan.org/~lds/Tie-DBI-0.93/lib/Tie/DBI.pm
--
So long... Fuzz
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Defining Field Types with view
Cheesy one here I created a view similar to Select field1, field2 from table where field3 = 'Fred' UNION Select field1, field2 from table where field3 = 'Wilma'; When the view contains just one sql statement the field definitions e.g. charater varying (20) however when both are in the fields turn into character varying (no size). this is causing a problem when referring to the fields within Dephi/VB because they think the varchar fields are memo. I have got around it by creating another view which selects field1::varchar(100) , field2 varchar(50) from firstview. I have tried casting the fields within the first view but to no sucess. Any way to get over this??? Cheers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Defining Field Types with view
"Traci Sumpter" <[EMAIL PROTECTED]> writes: > Cheesy one here Got a cheesy answer for you --- cast the result ... regression=# create view voo as regression-# select f1::varchar(20) from regression-# (select f1 from foo union select f2 from foo) ss; CREATE VIEW regression=# \d voo View "public.voo" Column | Type | Modifiers +---+--- f1 | character varying(20) | View definition: SELECT ss.f1::character varying(20) AS f1 FROM ( SELECT foo.f1 FROM foo UNION SELECT foo.f2 FROM foo) ss; 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
