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?
asked Feb 18, 2017 at 15:58
-
1en.wikipedia.org/wiki/E.164 is the standard– Neil McGuiganFeb 20, 2017 at 23:54
Add a comment
|
2 Answers
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)
answered Feb 18, 2017 at 15:58
-
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 pg_libphonenumber.
- Domain
phone.sqlfor validation phone number in internationalE.164format. - Function
phone_parse.sqlto parse phone number into 3 parts:country_code,area_code,local_number.
