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