Let's assume I want to store phone numbers in a database. I may accept phone numbers from outside of the United States. How would I go about storing these phone numbers?


2 Answers 2



When possible always use the canonical form. The more normalized the form the better. If there is a standard, use it. For this problem, let's use Google's libphonenumber, by proxy of pg-libphonenumber.

CREATE EXTENSION pg_libphonenumber;

This currently installs the phone_number type which has comparison operators and functions. It stores the number in an international canonical form. This is the best compromise in my opinion.

parse_phone_number('textnumber', 'CountryCode');

Because we can tell when phone numbers equal each other and we provide an internal normal form, we can do this..

SELECT parse_phone_number('03 7010 1234', 'AU') = parse_phone_number('(03) 7010 1234', 'AU');

(returns true). This also means that DISTINCT works so we can do this to get the effect you seem to want above.

  SELECT DISTINCT parse_phone_number(ph, 'AU')
    ('03 7010 1234'),
    ('(03) 7010 1234')
  ) AS t(ph);

That puts in..

 +61 3 7010 1234
(1 row)
  • 8
    Unfortunately, the package seems to be in alpha state for more than 1 year... High care recommended if you try to use it in production.
    – joanolo
    Feb 18, 2017 at 19:52
  • 4
    @joanolo You are right, but is seems that a new contributor, Evan Carroll, has made a new commit just today... Somebody would consider all this self-promotion on Evan’s part but I upvoted this self-answer also because he is actively contributing to the extension...
    – Dario
    Feb 20, 2017 at 17:25
  • 3
    I only "contributed" to the documentation. And, I did so before I posted this in the same fashion I always do to improve documentation. I'm not a "contributor", in any useful sense. I don't have a commit bit. I don't write C++ code. I'm just passionate about having libphonenumber binding in PgSQL and others knowing about them and how to use them. Feb 20, 2017 at 17:36
  • 6
    Contributing to the documentation is contributing. ;) And I’ll use libphonenumber in a (safe, non critical) project of mine because of this post. Thank you.
    – Dario
    Feb 20, 2017 at 18:13
  • 5
    For what its worth, we use libphonenumber to normalize the number before it goes into the database - that way we don't need to be dependent on (at the time of writing) alpha-quality postgres plugins which can't be used in RDS and other cloud-based postgres deployments. Feb 23, 2017 at 3:07

There is a solution without extension pg_libphonenumber.

  • Domain phone.sql for validation phone number in international E.164 format.
  • Function phone_parse.sql to parse phone number into 3 parts: country_code, area_code, local_number.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.