Received: from gatekeeper.embratel.com.br (gatekeeper.embratel.com.br [200.255.122.2]) by hub.org (8.8.8/8.7.5) with SMTP id RAA04913 for ; Thu, 3 Sep 1998 17:01:16 -0400 (EDT) Received: by gatekeeper.embratel.com.br; (5.65v3.2/1.3/10May95) id AA18382; Thu, 3 Sep 1998 18:13:11 -0300 X400-Received: by mta ebtrj in /PRMD=EBT/ADMD=EMBRATEL/C=BR/; Relayed; 3 Sep 1998 21:02:28 +0000 X400-Received: by /PRMD=EBT/ADMD=EMBRATEL/C=BR/; Relayed; 3 Sep 1998 17:54:41 +0000 X400-Received: by mta "SCI02.MUAS" in /PRMD=EBT/ADMD=EMBRATEL/C=BR/; Relayed; 3 Sep 1998 17:53:46 +0000 X400-Received: by mta SCI02 in /PRMD=EBT/ADMD=EMBRATEL/C=BR/; Relayed; 3 Sep 1998 17:53:46 +0000 X400-Received: by mta GO686 in /PRMD=EBT/ADMD=EMBRATEL/C=BR/; Relayed; 3 Sep 1998 17:54:25 +0000 Date: 3 Sep 1998 17:54:25 +0000 X400-Originator: fidelis@mailgate.embratel.com.br X400-Mts-Identifier: [/PRMD=EBT/ADMD=EMBRATEL/C=BR/; 4246531703091998/A01229/SCI02] Discarded-X400-Mts-Extensions: (43) (12) (2) (135) (115) (5) (6) (3) From: "Fidelis Assis" (Tel +55 21 519 8408) To: owner-pgsql-sql@hub.org, "Fidelis Assis" , owner-pgsql-sql@hub.org, pgsql-sql@postgreSQL.org In-Reply-To: Subject: Resp: RE: Resp: [SQL] Concatenation in selects Importance: normal Sensitivity: Company-Confidential Autoforwarded: FALSE Message-Id: <4246531703091998/A01229/SCI02/11C91C752D00*@MHS> Original-Encoded-Information-Types: (1) (0) (10021) (7) (1) (0) (100), (1) (0) (10021) (7) (1) (0) (6), (1) (0) (10021) (7) (1) (0) (1) Content-Identifier: 11C91C752D00 X400-Content-Type: P2-1988 (22) Priority: normal Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable > >> select ltrim(first_name) || (' ' || ltrim(last_name)) as full_na= me >> from >> ... >> >> where "ltrim" is used only to convert both, first_time and >> last_time, to text. I wish I could convert among string types >> (bpchar, varchar and text) without any tricks, but it seems this >> is not implemented yet. >> >Ever tried the cast() function? > -DEJ > Yes, but I haven=B4t been successful. This is an example of what I have tried: httpd=3D> create table t1 (f1 char(4), f2 varchar(4), f3 text); CREATE httpd=3D> \d t1 Table =3D t1 +----------------------------+----------------------------+-------+= | Field | Type | Length| -----------------------------+----------------------------+-------+ | f1 | char() | 4 | | f2 | varchar() | 4 | | f3 | text | var | +----------------------------+----------------------------+-------+ httpd=3D> insert into t1 values ('1234', '5678', '9012'); INSERT 648441 1 httpd=3D> select f1 || f1 from t1; ?column? -------- 12341234 (1 row) OK, when both operands have the same type, but httpd=3D> select f1::varchar || f2 from t1; ERROR: Sep 3 16:53:00:function varchar(bpchar) does not exist ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ On the other hand, conversions to integer do work: httpd=3D> select f1::int2 + f2::int2 from t1; ?column? -------- 6912 (1 row) finally, httpd=3D> select (f1 || f1) || f3 from t1; ERROR: Sep 3 17:15:36:There is no operator '||' for types 'bpchar= ' and 'text' You will either have to retype this query using an explicit= cast, or you will have to define the operator using CREATE OPERAT= OR The result of (f1 || f1) is of type bpchar, but f3 is of type text. httpd=3D> select (f2 || f2) || f3 from t1; ERROR: Sep 3 17:16:20:There is no operator '||' for types 'varcha= r' and 'text' You will either have to retype this query using an explicit= cast, or you will have to define the operator using CREATE OPERAT= OR The result of (f2 || f2) is of type varchar, but f3 is of type text.= httpd=3D> select (f3 || f3) || f3 from t1; ?column? ------------ 901290129012 (1 row) OK, because all operands have the same type. It=B4s interesting that, despite the documentation (\do) states that= the result of the operator "||" is of type text, it's allways resulting = a value with the same type as of its operands. It=B4s worth noting that the function "textcat" works with any type = of operand. =46idelis Assis MCI-Embratel - Rio de Janeiro