PostgreSQL – How to Store Phone Numbers Correctly

data integritydata validationdatatypespostgresql

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?

Best Answer

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

Related Solutions

Mysql – Storing phone number in E.164

BIGINT(15) or even BIGINT(18) might not be long enough to store all possible phone numbers. Depending which country and if a PBX is involved 18 digits are not going to be enough. I have seen quite a number of longer ones already. Even so E.164 recommends to not assign phone numbers with more than 15 digits to phone providers world wide, not all of them adhere to these standards.

I am not sure where exactly you are heading with your database, but if you want to store phone numbers I would strongly suggest to use a character based format. In most applications that really use phone numbers you might not only want to store them but also use them. For using a phone number you might need some transformations to add, change or replace prefixes, or maybe filter for certain prefixes, since international phone numbers do not have all the same length it is much easier and faster on the database to use a character based format like CHAR(32) or VARCHAR(32). If speed of character based filter operations (number pattern, prefixes, areacodes, etc) is important for you the CHAR typ will usually outperform VARCHAR but you will have to clean up trailing spaces. Internationally there are two common ways to realize direct in dial lines. I would call them "direct dial" and "post connection dial". Any direct dial number can be put into the "regular" number field since they can be dialed in one succession with the carrier assigned number. The other type usually requires a pause before continuing to dial the extension (like in most smaller US legacy PBX systems) these "post dial digits" should go into a separate field, which for better design should be nullable. I would not use the E.164 extension format that you mentioned, it is so rarely used that in myself working in the industry for well over 10 Years I have never seen it in production.

PostgreSQL – How to Best Store a Timestamp

Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around:

To address your concern:

passing a correctly formatted timestamp is more complex than a simple number

You can pass and retrieve a UNIX epoch either way if you prefer:

SELECT to_timestamp(1437346800)
     , extract(epoch FROM timestamptz '2015-07-20 01:00+02');

If you want to store the current timestamp with writes to the DB, use a timestamptz column with default value now(). The system time on the DB server is typically much more reliable and consistent than multiple clients handing in their respective notion of what time it is.
For INSERT it can be as simple as:

CREATE TABLE foo (
  ... -- other columns
, created_at timestamptz NOT NULL DEFAULT now()
);

And just don't write to that column. It's filled in automatically.

Best Answer

libphonenumber

Related Solutions

Mysql – Storing phone number in E.164

PostgreSQL – How to Best Store a Timestamp

Related Question

`libphonenumber`