Ow Mun Heng wrote:
>> I want to change a column type from varchar(4) to varchar(5) or should I
>> just use text instead.
Eric Bangug wrote:
> ALTER TABLE tablename ALTER COLUMN columnname TYPE VARCHAR(5);
Please post in plain text, not HTML, particularly not HTML with
<font size="1">
It's also both polite and helpful to later readers to attribute citations,
such as the one from Ow Mun Heng above.
Since you answered the first part of the question, I will essay the second part.
The choice of TEXT for the column would seem to be supported in the PG manual,
which stresses that TEXT and VARCHAR are quite close in performance, if not
identical. I recommend to constrain the length if it's proper for the data
domain. That is, if you are 100% absolutely certifiably certain that the
length will never change again once you set it to 5, that is, if the data
domain is a set of values that must be no more than 5 characters long, then
VARCHAR(5) is a good choice. It accurately represents the data.
If you cannot know that based on the domain analysis, then another length is
better. If the length is truly open-ended, then TEXT is the way to go.
It isn't necessary to use TEXT to represent license plate information, for
example, because we know that it will never exceed a maximum length. U.S.
states limit license plate strings to 8 characters or fewer. International
standards vary, but I have never seen a 15-character license plate string, and
I am confident that 30 is too long. There's only so much that can fit in the
width of a vehicle and still be readable by the police officer behind you.
Some research into license plate standards worldwide would be needed, but
there would be a maximum length and I would represent that maximum in the
column type, maybe with a little extra just in case the future brings more.
It's a question of what represents the data most accurately and completely.
--
Lew
