pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Fidelis Assis <fidelis@embratel.com.br>
To: owner-pgsql-sql@hub.org, "Fidelis Assis" <fidelis@embratel.com.br>
To: owner-pgsql-sql@hub.org
To: pgsql-sql@postgreSQL.org
Subject: Resp: RE: Resp: [SQL] Concatenation in selects
Date: 3 Sep 1998 17:54:25 +0000
Message-ID: <4246531703091998/A01229/SCI02/11C91C752D00*@MHS> (raw)
In-Reply-To: <F10BB1FAF801D111829B0060971D839F3E2D33@cpsmail>

>
>>  select ltrim(first_name) || (' ' || ltrim(last_name)) as full_name
>> 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´t been successful.

This is an example of what I have tried:

 httpd=> create table t1 (f1 char(4), f2 varchar(4), f3 text);
 CREATE
 httpd=> \d t1
 
 Table    = t1
 +----------------------------+----------------------------+-------+
|        Field               |        Type                | Length|
-----------------------------+----------------------------+-------+
| f1                         | char()                     |     4 |
| f2                         | varchar()                  |     4 |
| f3                         | text                       |   var |
+----------------------------+----------------------------+-------+

 httpd=> insert into t1 values ('1234', '5678', '9012');
 INSERT 648441 1

 httpd=> select f1 || f1   from t1;
 ?column?
 --------
 12341234
 (1 row)
 
OK, when both operands have the same type, but


 httpd=> 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=> select f1::int2 + f2::int2 from t1;
 ?column?
 --------
     6912
 (1 row)

finally,

 httpd=> 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 OPERATOR

The result of (f1 || f1) is of type bpchar, but f3 is of type text.


 httpd=> select (f2 || f2) || f3 from t1;
 ERROR:  Sep  3 17:16:20:There is no operator '||' for types 'varchar' and
'text'
         You will either have to retype this query using an explicit cast,
         or you will have to define the operator using CREATE OPERATOR

The result of (f2 || f2) is of type varchar, but f3 is of type text.


 httpd=> select (f3 || f3) || f3 from t1;
     ?column?
 ------------
 901290129012
 (1 row)

OK, because all operands have the same type.


It´s 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´s worth noting that the function "textcat" works with any type of
operand.


Fidelis Assis
MCI-Embratel  - Rio de Janeiro



Message-ID: <4246531703091998/A01229/SCI02/11C91C752D00*@MHS>
Permalink:  ../4246531703091998%2FA01229%2FSCI02%2F11C91C752D00%2A@MHS/
Also on:    postgresql.org/message-id/4246531703091998/A01229/SCI02/11C91C752D00*@MHS

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: fidelis@embratel.com.br, owner-pgsql-sql@hub.org
  Subject: Re: Resp: RE: Resp: [SQL] Concatenation in selects
  In-Reply-To: <4246531703091998/A01229/SCI02/11C91C752D00*@MHS>
  References: <F10BB1FAF801D111829B0060971D839F3E2D33@cpsmail> <4246531703091998/A01229/SCI02/11C91C752D00*@MHS>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox