Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: Field Display Width

    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?

  2. #2
    Join Date
    May 2008
    Posts
    277
    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)?

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    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 View Post
    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?

  4. #4
    Join Date
    May 2008
    Posts
    277
    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.
    Last edited by futurity; 02-01-12 at 12:25.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •