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

    Unanswered: What Data Type For Phone #'s?

    I am configuring a database on my PostgreSQL server and was trying to find out what the proper configuration is for entering a phone # into a table would be?

    555-555-5555

    So for the above, would I simply just want to select 'varchar' for my data type on this field or what is the common used data type for a field of just phone #'s?

    PostgreSQL: Documentation: Manuals: PostgreSQL 8.1: Data Type Formatting Functions
    Last edited by CacheDrive; 11-29-10 at 16:32.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Do you want to save the dashes??? Storing 'format' data seems to be wasteful of disk space, and it leads to search difficulties when users are allowed to freely enter characters. I normally store just the digits 0-9 in a varchar(12) field, and add formatting in views when I retrieve the data. That way, you can support other formats - even international formats.

    Do you want to support international calls? (If so, a column for a country code will probably be needed.) Likewise for an extension. Per E.164, up to 12 digits could be required, excluding up to three digits for the country code.

    In pg, you could create a function to return a 'properly' formatted phone number, which bases the format upon the country code field, and a view which uses that function...
    Last edited by loquin; 11-29-10 at 18:15.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's VARCHAR, whether you store the formatting or not (i recommend that you do, if international numbers are a possibility)

    it's certainly not numeric, since there is never a need (in my experience) to find the sum of all phone numbers or the average phone number

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    Question

    Thanks all. I just did 'varchar(12) which I assume is completely acceptable in the DBA community.

    This is my 1st database from scratch, does it look okay?

    Code:
    ghost=# \d contacts
               Table "public.contacts"
     Column  |         Type          | Modifiers 
    ---------+-----------------------+-----------
     id      | smallint              | not null
     fname   | character varying(40) | not null
     lname   | character varying(40) | not null
     email   | character varying(40) | not null
     cell    | character varying(12) | 
     company | character varying(40) | 
     display | character varying(40) | 
    Indexes:
        "contacts_pkey" PRIMARY KEY, btree (id)
        "contacts_cell_key" UNIQUE, btree (cell)
        "contacts_email_key" UNIQUE, btree (email)

  5. #5
    Join Date
    May 2008
    Posts
    277
    Since PostgreSQL has built-in support for regular expressions, you can add a constraint to make sure the phone number column contains sensible data:

    Code:
    create table contacts(
        ....
        cell varchar(12) check (cell ~ '^[0-9]+$'),
        ....
    )
    I also tend to prefer TEXT fields over VARCHAR fields with arbitrary string lengths, although TEXT is not a standard datatype. I'm sure there are probably differing opinions on this.

  6. #6
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Thank you for your input!

Posting Permissions

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