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?
1en.wikipedia.org/wiki/E.164 is the standard– Neil McGuiganFeb 20, 2017 at 23:54
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.
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)
8Unfortunately, the package seems to be in alpha state for more than 1 year... High care recommended if you try to use it in production.– joanoloFeb 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...– DarioFeb 20, 2017 at 17:25
3I 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
6Contributing to the documentation is contributing. ;) And I’ll use libphonenumber in a (safe, non critical) project of mine because of this post. Thank you.– DarioFeb 20, 2017 at 18:13
5For 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
phone.sqlfor validation phone number in international
phone_parse.sqlto parse phone number into 3 parts: