character varying == text?

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?