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