Field Display Width

CarlosinFL · #1 (**permalink**) 01-31-12, 23:16

When viewing data using psql, I notice the fields set to 'char' have an excessive amount of white space:

Code:

nexus=# SELECT * FROM music;
 id |       band        |          album           |    date    |                        asin                        |    label
----+-------------------+--------------------------+------------+----------------------------------------------------+--------------
  1 | Dance Gavin Dance | Downtown Battle Mountain | 2007-05-15 | B000OQF4PQ                                         | Rise Records
(1 row)

When I change the field from char(50) to varchar(50), there's no more white space:

Code:

nexus=# SELECT * FROM music;
 id |       band        |          album           |    date    |    asin    |    label
----+-------------------+--------------------------+------------+------------+--------------
  1 | Dance Gavin Dance | Downtown Battle Mountain | 2007-05-15 | B000OQF4PQ | Rise Records
(1 row)

I did some searching and I was told to use 'char' when I know the values will all be similar in length. Well all ASIN are identical in length so I decided to use char. Was that not a good field type for the length?

futurity · #2 (**permalink**) 02-01-12, 10:23

CHARs are always padded with spaces up to the specified number of characters (contrast this with VARCHAR and TEXT types). So in your first example, you actually have a 50-character long string, and psql i sonly making the column wide enough to display it.

The question is, if you are only storing 10-character long strings, why did you declare your type to be CHAR(50)?

CarlosinFL · #3 (**permalink**) 02-01-12, 11:00

Ah OK. I understand now. I didn't understand what the manual meant by "blank padded" but now it's fairly obvious.

Quote:

Originally Posted by futurity

The question is, if you are only storing 10-character long strings, why did you declare your type to be CHAR(50)?

Easy answer...I have no idea what I'm doing when it comes to ANSI SQL.

My justification: I assumed wrong in thinking that there could be a chance at some later point that the field 'asin' could have more character stings so I said "what the heck, just make it more than enough and it should be fine." I didn't chose 'varchar' because the 'asin' field is always comprised of letters / numbers with no spaces, special characters, or anything else. I'm guessing I need to just change this then to 'char(10)' and address character length once it becomes an issue, correct?

futurity · #4 (**permalink**) 02-01-12, 11:20

I only use PostgreSQL's TEXT data type (although it's non-standard) unless the string is actually length limited. Some examples are the United State's Post Office's 2-letter state codes, or a social security number.

I'm not familiar enough with ASINs to make a recommendation one way or another, but you could easily enforce the requirement you've described as follows:

Code:

create table foo (
    ...
    asin text not null check (asin ~ '^[A-Z0-9]{10}$')
);

If the requirement ever changes, you simply need to change the check constraint. Changing the data type could be more problematic, especially if it's part of a foreign key.

Internet Services

Web Development

Digital Marketing

Consumer Tech