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?
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)