Re: character varying == text?

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> CSN <cool_screen_name90001(at)yahoo(dot)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