Обсуждение: moving char() to varchar()

Hi,

The developers of our application changed a number of fields from
char() to varchar() (which was a Good Thing, for reasons I won't bore
you with).  Now, I thought I could just do a pg_dump -a on the
database, create the new schema, and load the old data into the new
schema, and the new varchar() columns would be trimmed.  They're
not, however: they get blank-padded to the old length.  Obviously,
the answer is to go through and trim() all the columns; not a
problem.  But I have two questions.

1.    I thought the SQL spec required varchar() not to pad.  Is it
just that, because of the way pg_dump saved the char() data (as
blank-padded) that the varchar() field preserves the padded data?

2.    I could _swear_ I did something very similar to this some
time ago (version 6.5.x?  something like that?).  Am I just imagining
things?  (I'm perfectly prepared to accept that, by the way.  My
memory is about as reliable these days as the DIMM I took out of my
PC last week.  That's why I need a good DBMS like postgres!)

A
--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.com>                               M6K 3E3
                                         +1 416 646 3304 x110

> 1. I thought the SQL spec required varchar() not to pad.  Is it
> just that, because of the way pg_dump saved the char() data (as
> blank-padded) that the varchar() field preserves the padded data?

A dump from a char() field keeps the NULL padding even in the dump file I
assume, so when you went to import it you were importing data with NULLs
attached..

> 2. I could _swear_ I did something very similar to this some
> time ago (version 6.5.x?  something like that?).  Am I just imagining
> things?  (I'm perfectly prepared to accept that, by the way.  My
> memory is about as reliable these days as the DIMM I took out of my
> PC last week.  That's why I need a good DBMS like postgres!)

I can't say one way or another on that.. But I feel your pain on the memory
problems :-)

-Mitch

--- Andrew Sullivan <andrew@libertyrms.com> wrote:
> 1.    I thought the SQL spec required varchar() not to pad.  Is
> it
> just that, because of the way pg_dump saved the char() data
> (as
> blank-padded) that the varchar() field preserves the padded
> data?

The pg_dump utility dumps out the data as is so all CHAR columns
would be dumped with the accompanying padding spaces.

Brent

__________________________________________________
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com

On Fri, 7 Sep 2001, Andrew Sullivan wrote:

> 1.    I thought the SQL spec required varchar() not to pad.  Is it
> just that, because of the way pg_dump saved the char() data (as
> blank-padded) that the varchar() field preserves the padded data?
Yes. varchar preserves padding, but does not add additional. char does add
additional.

> 2.    I could _swear_ I did something very similar to this some
> time ago (version 6.5.x?  something like that?).  Am I just imagining
> things?  (I'm perfectly prepared to accept that, by the way.  My
> memory is about as reliable these days as the DIMM I took out of my
> PC last week.  That's why I need a good DBMS like postgres!)
Similar to what?

Andrew Sullivan <andrew@libertyrms.com> writes:
> 1.    I thought the SQL spec required varchar() not to pad.  Is it
> just that, because of the way pg_dump saved the char() data (as
> blank-padded) that the varchar() field preserves the padded data?

Right.  Trailing blanks in the presented data *should* be preserved
by varchar; they're valid data, not pad, as far as the DB knows.

You could possibly make an argument that trailing blanks in a char()
column (which ARE known to be padding) should be stripped during dumping,
primarily for convenience in reloading into varchar columns.  But this
seems a tad weird and unexpected to me.  An explicit trim() operation
sounds like a better idea.

> 2.    I could _swear_ I did something very similar to this some
> time ago (version 6.5.x?  something like that?).

Could be.  I think we've tweaked the behavior a few times to get closer
to the SQL92 spec.

            regards, tom lane

On Fri, 7 Sep 2001, Tom Lane wrote:

> Andrew Sullivan <andrew@libertyrms.com> writes:
> > 1.    I thought the SQL spec required varchar() not to pad.  Is it
> > just that, because of the way pg_dump saved the char() data (as
> > blank-padded) that the varchar() field preserves the padded data?
>
> You could possibly make an argument that trailing blanks in a char()
> column (which ARE known to be padding) should be stripped during dumping,
> primarily for convenience in reloading into varchar columns.  But this
> seems a tad weird and unexpected to me.  An explicit trim() operation
> sounds like a better idea.

Maybe you can modify the pg_dump source to use trim() on the output
columns when given a --trim-char-columns :-)

--
Alvaro Herrera (<alvherre[@]atentus.com>)