13

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?

20

libphonenumber

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.

CREATE TABLE foo
AS
  SELECT DISTINCT parse_phone_number(ph, 'AU')
  FROM ( VALUES
    ('0370101234'),
    ('03 7010 1234'),
    ('(03) 7010 1234')
  ) AS t(ph);
SELECT 1

That puts in..

 parse_phone_number 
--------------------
 +61 3 7010 1234
(1 row)
  • 7
    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 '17 at 19:52
  • 2
    @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 '17 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. – Evan Carroll Feb 20 '17 at 17:36
  • 5
    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 '17 at 18:13
  • 4
    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. – John Hamelink Feb 23 '17 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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