Received: from mailbox.orfinet.cz ([195.119.181.60]) by hub.org (8.9.2/8.9.1) with ESMTP id MAA32763; Tue, 13 Apr 1999 12:17:35 -0400 (EDT) (envelope-from davids@orfinet.cz) Received: from chameleon. ([195.119.181.29]) by mailbox.orfinet.cz (8.8.5/8.8.5) with ESMTP id SAA16310; Tue, 13 Apr 1999 18:19:46 +0200 Received: (from david@localhost) by chameleon. (8.9.3/8.9.3) id SAA02633; Tue, 13 Apr 1999 18:16:54 +0200 To: "Glenn Waldron" Cc: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org Subject: bug report on text text fields. WAS: Re: [SQL] Trouble with ... References: <004401be85c1$e21175d0$0800a8c0@glenn.kivex.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii From: David Sauer Date: 13 Apr 1999 18:16:53 +0200 In-Reply-To: "Glenn Waldron"'s message of "Tue, 13 Apr 1999 11:25:37 -0400" Message-ID: Lines: 59 User-Agent: Gnus/5.07008 (Pterodactyl Gnus v0.80) Emacs/20.3 >>>> "Glenn" == Glenn Waldron writes: Glenn> Using Postgres 6.5 beta (snap 12 apr), on Linux i386. I moved up from Glenn> 6.4.2 when I couldn't get things working. Glenn> I'm having difficulty dealing with null text/varchar fields. I need Glenn> to be able to interpret null values as the null string '' for the Glenn> purposes on concatenation. Glenn> 1) ---- Glenn> For example, the query: Glenn> SELECT (field_one || field_two) from t1; Glenn> Will return the concatenation of the two fields. If either of the fields Glenn> is null, it is interpreted as the empty string '' and the correct answer Glenn> is printed. Correct ? I have a table: david=> \d t Table = t +----------------------------------+----------------------------------+-------+| Field | Type | Length|+----------------------------------+----------------------------------+-------+| a | text | var || b | text | var |+----------------------------------+----------------------------------+-------+ david=> insert into t values (NULL, '1111'); INSERT 26656 1 david=> insert into t values ('2222', NULL); INSERT 26657 1 david=> select a||b from t; ?column? -------- (2 rows) But I expect something like: 1111 2222 not 2 empty strings. It is wrong ? ================================================ And yet, I found bug in cast. Try on table above: select a::int from t; ... this will crash backend on field with NULL value .... I have postgres 6.5beta1 compiled by egcs-1.1.2 on linux box with IBM 6x86L processor ... -- * David Sauer, student of Czech Technical University * electronic mail: davids@orfinet.cz (mime compatible)