主页 / dba / 问题 / 305271
Accepted
yayaur
Asked: 2021-12-22 19:36:23 +0800 CST

如何在 postgres 中仅存储美国/加拿大电话号码？

  • 772

我只想在 Postgres TEXT 列中存储美国和加拿大的电话号码。我不想使用pg_libphonenumber.

为此，我应该使用什么 CHECK 约束？

postgresql check-constraints

1 个回答

  1. Best Answer

    你需要在REGULAR EXPRESSION这里掌握 s 。现在，PostgreSQL 在这个领域提供了一系列可能性，它远远超出了这里的答案范围来教授关于正则表达式的所有内容——它本身就是计算机科学的整个领域——恕我直言，这是更好的教程网站之一。

    首先要做的事情——电话号码是一个字符串（PostgreSQL 方言中的 TEXT——其他人中的 VARCHAR()）——一个不会加、减、乘或除电话号码。此外，其中可能出现0-9 以外的字符（即(、)、-和.空格）。

    正如@LaurenzAlbe 指出的那样，清楚地了解实际需求是件好事。因此，您需要检查您的字符串以确保它们对应于北美电话号码（下面的所有代码都可以在此处的小提琴中找到）！

    北美电话号码的长度为 10 位，通常由空格分隔为三组，每组 3 位、3 位和 4 位。

    因此，作为第一个近似值，您可以执行以下操作：

    CREATE TABLE phnum_1
(
  num TEXT NOT NULL
  
  CONSTRAINT num_1_ck_1 
    CHECK (num ~ '^\d{3} \d{3} \d{4}$')

  CONSTRAINT num_1_ck_2
    CHECK (num ~ '^[0-9]{3} [0-9]{3} [0-9]{4}$')    

 CONSTRAINT num_1_ck_3
    CHECK (num ~ '^[[:digit:]]{3} [[:digit:]]{3} [[:digit:]]{4}$')
);

    请注意，所有这三个CONSTRAINTs 都做同样的事情——只是表达方式不同。

    这些是非常简单的正则表达式（见下文），您只需确保每个条目num必须以 3 位数字开头，后跟一个空格，后跟 4 位数字，另一个空格和 4 个最终数字。

    正则表达式的解释：

    • ^- 是一个"anchor"- 它指的是要检查的字符串的开头。
    • \d（或 [0-9] 或 [[:digit:]]）是数字字符的简写 - （即 0、1、2...、9）
    • {n}是一种说法n，并且只n出现前一件事 - 在这种情况下，是一个数字 - 例如，您可以说，{2,4}这意味着您的匹配出现 2 到 4 次。在上面，您可以根据需要使用冗余的 {3,3}/{4,4} 吗？
    • 然后是一个空格 - 文字空格字符 - 它在正则表达式中没有特殊含义。
    • 然后\d{4} \d{4}- 4 位数字，一个空格，然后再增加 4 位数字。
    • 最后，另一个锚点——$作为字符串标记结尾的字符！

    因此，正如您从小提琴中看到的那样，按原样'123 345 3434'接受987 654 3210，但'123-234-5678'被拒绝。

    所以，这个正则表达式很简单，但非常严格。现在，这一特殊要求的复杂程度可能会很快变得平流层......

    有左括号和右括号（(或)）（国际代码），+或者在国际代码之前没有任何内容），本地交换代码是有效的，是连字符（-）还是不是在数字组之间？真的，可能性几乎是无限的......

    我敦促您在这里查看，看看我们的姊妹网站 StackOverflow 上是如何处理一些/许多这些棘手问题的。

    我已将其中一些正则表达式放入小提琴中，如下所示：

    CREATE TABLE phnum_2 
(
  num TEXT NOT NULL
);

    并用一些样本潜在数字填充它：

    INSERT INTO phnum_2 VALUES
('123-456-7890'),
('987 654 3210'),
('123-234-5678'),
('+1 123 456 7890'),
('+353 123 456 7890');

    然后运行这个查询：

    SELECT 
  num ~ '\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$' AS re1,
  num ~ '^(\+\d{1,2}\s)?((\(\d{3}\))|(\d{3}))[\s.-]\d{3}[\s.-]\d{4}$' AS re2,
  num ~ '^\s*(?:\+?(\d{1,3}))?[-. (]*(\d{3})[-. )]*(\d{3})[-. ]*(\d{4})(?: *x(\d+))?\s*$' AS re3,
  num ~ '^(\+\d{1,2}\s?)?1?\-?\.?\s?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$' AS re4,
  num ~ '^(\+1\s?)?1?\-?\.?\s?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$' AS re5,
  num ~ '^\s*(?:\+?(\d{1,3}))?[-. (]*(\d{3})[-. )]*(\d{3})[-. ]*(\d{4})(?: *x(\d+))?\s*$' AS re6,
  num ~ '(\+\d{1,3}\s?)?((\(\d{3}\)\s?)|(\d{3})(\s|-?))(\d{3}(\s|-?))(\d{4})(\s?(([E|e]xt[:|.|]?)|x|X)(\s?\d+))?' AS re7
FROM 
  phnum_2;

    结果：

    re1     re2     re3     re4     re5     re6     re7
  t       t       t       t       t       t       t
  t       t       t       t       t       t       t
  t       t       t       t       t       t       t
  t       t       t       t       t       t       t
  t       f       t       f       f       t       t

    我会敦促你看看那些失败的，并试图找出他们失败的原因！

    这里有几个不太复杂的，更复杂的线程可以在这里找到，这个站点提供了很多可能性，包括这个怪物：

    ^(\+?1(-|\.|\s)?)?((\(((8(00|22|33|44|55|66|77|[8[0-9]))|900)\)|((8(00|22|33|44|55|66|77|[8[0-9]))|900))(-|\.|\s)?\d{3}(-|\.|\s)?\d{4}|(\([2-9]([02-9]\d|1[02-9])\)|[2-9]([02-9]\d|1[02-9]))(-|\.|\s)?[2-9]([02-9]\d|1[02-9](-|\.|\s)?\d{4}))$

    但是，我将把最后一句话留给这位指出：

    如果用户想给你他们的电话号码，那么相信他们会做对。如果他们不想给你，那么强迫他们输入一个有效的数字会将他们发送到竞争对手的网站，或者让他们输入一个适合你的正则表达式的随机字符串。我什至可能会想查找收费率占星热线的号码，然后输入。

    我还将以下任何一项视为网站上的有效条目：

    • “123 456 7890 至下午 6 点，然后拨打 098 765 4321”
    • “123 456 7890 或试试我的手机 098 765 4321”
    • “前目录 - 管好自己的事”

    此外，不要忘记正则表达式在处理能力方面的成本很高- 请参阅 StackExchange 的一位创始人的这篇文章，以及如何（部分）从StackOverflow 上最多产的正则表达式回答者之一那里减轻这种情况。

    所以，您真的应该考虑您的要求 - 以及最终答案将如何存储 - 作为自由文本还是严格作为一系列 10 [有效] 数字？表中的数据越干净，使用优化索引策略的潜力就越大。

    • 1

相关问题