Re: character varying == text? - Mailing list pgsql-general

Scott Marlowe wrote:
> On Mon, 2005-09-19 at 12:54, Alex Turner wrote:
>> I thought a char field was supposed to return a padded string, and
>> varchar was supposed to return a non-padded string?
>>
>> I just checked though:
>>
>> create table test (
>> stuff char(10)
>> );
>>
>> insert into test values ('foo');
>>
>> select stuff || 'lemon' from test;
>>
>> This returns 'foolemon', not 'foo        lemon' as I would have
>> expected.
>>
>> Alex Turner
>> NetEconomist
>>
>> On 9/15/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>         CSN <cool_screen_name90001@yahoo.com> writes:
>>         > Just something I was curious about - is there any
>>         > difference at all between "character varying" (in the
>>         > SQL spec) without a length specified and "text" (not
>> > in the SQL spec)?
>>
>>         The SQL standard doesn't allow "character varying" without a
>> length spec.
>>
>>         But yeah, in Postgres they're essentially the same thing.
>>
>>                                 regards, tom lane
>>
>>         ---------------------------(end of
>>         broadcast)---------------------------
>>         TIP 9: In versions below 8.0, the planner will ignore your
>>                desire to choose an index scan if your joining
>>                column's datatypes         do not match
>>
>
> That's because || is a text operator, not a char operator
> here.  So, what's really happening is:
>
> select cast(charfield as text)||cast(textfield as text)
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

is this the expected result? i.e. in the past was the result incorrect?
or is there a configuration param that controls this?


test=# select stuff || 'lemon' from test1;
    ?column?
-----------------
 foo       lemon
(1 row)

test=# \d test1
           Table "test1"
 Column |     Type      | Modifiers
--------+---------------+-----------
 stuff  | character(10) |

test=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

----
test=# select stuff || 'lemon' from test;
 ?column?
----------
 foolemon
(1 row)

test=# \d test
        Table "public.test"
 Column |     Type      | Modifiers
--------+---------------+-----------
 stuff  | character(10) |

test=# select version();
                                         version

------------------------------------------------------------------------
--------
----------
 PostgreSQL 8.0.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw
-special)
(1 row)

reid