19

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?

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479

2 Answers2

25

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)
Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
  • 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 '17 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 '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
  • 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 '17 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. – John Hamelink Feb 23 '17 at 03:07
2

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.
Rinat
  • 129
  • 3