Thread: character varying == text?

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)?

Thanks,
CSN



__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

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

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 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)

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

"Reid Thompson" <Reid.Thompson@ateb.com> writes:
> is this the expected result? i.e. in the past was the result incorrect?
> or is there a configuration param that controls this?

Yes, yes, and no.  You could change the pg_cast entry for char-to-text
if you wanted, but then you would run into the other unpleasant cases
that prompted us to change it in the first place.

            regards, tom lane

On Mon, 2005-09-19 at 15:38, Tom Lane wrote:
> "Reid Thompson" <Reid.Thompson@ateb.com> writes:
> > is this the expected result? i.e. in the past was the result incorrect?
> > or is there a configuration param that controls this?
>
> Yes, yes, and no.  You could change the pg_cast entry for char-to-text
> if you wanted, but then you would run into the other unpleasant cases
> that prompted us to change it in the first place.

I seem to recall this is how the SQL spec says it should happen.  Is
that my imagination, or did that subject come up before?