blocks|key|1918498|text|CREATE+TABLE+emails+(
++++email+varchar
++++CONSTRAINT+proper_email+CHECK+(email+~*+'%5E[A-Za-z0-9._%25-]%2B@[A-Za-z0-9.-]%2B[.][A-Za-z]%2B$')
);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1918499|(regex可能不完整，您可以在网上搜索regexp以查找匹配的电子邮件，然后选择您最喜欢的)。|unstyled|1918500|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>CREATE TABLE emails (
 email varchar
 CONSTRAINT proper_email CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
);
</code></pre>

(regex may be incomplete, you can search for regexp for email matching all over the web and pick the one you like best).

blocks|key|2315947|text|我建议使用现有的电子邮件地址解析模块，而不是构建自己的模式匹配。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2315948|CREATE+OR+REPLACE+FUNCTION+check_email(email+text)+RETURNS+bool
LANGUAGE+plperlu
AS+$$
use+Email::Address;

my+@addresses+=+Email::Address->parse($_[0]);
return+scalar(@addresses)+>+0+?+1+:+0;
$$;

CREATE+TABLE+emails+(
++++email+varchar
++++CONSTRAINT+proper_email+CHECK+(check_email(email))
);|code-block|syntax|javascript|2315949|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I recommend using an existing email address parsing module instead of making up your own pattern matching. For example:

<pre><code>CREATE OR REPLACE FUNCTION check_email(email text) RETURNS bool
LANGUAGE plperlu
AS $$
use Email::Address;

my @addresses = Email::Address-&gt;parse($_[0]);
return scalar(@addresses) &gt; 0 ? 1 : 0;
$$;

CREATE TABLE emails (
 email varchar
 CONSTRAINT proper_email CHECK (check_email(email))
);
</code></pre>

blocks|key|2315966|text|您还可以创建domain，并在定义表列时将其用作类型，例如|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2315967|CREATE+DOMAIN+email+AS+TEXT+CHECK+(VALUE+~*+'%5E[A-Za-z0-9._%25-]%2B@[A-Za-z0-9.-]%2B[.][A-Za-z]%2B$');

CREATE+TABLE+emails+(
++++email+email
);|code-block|syntax|javascript|2315968|这样，每次在数据库中使用包含列的电子邮件时，就不需要重新定义正则表达式。|2315969|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/9.5/static/sql-createdomain.html^0|6|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

You can also create a <a href="https://www.postgresql.org/docs/9.5/static/sql-createdomain.html" rel="noreferrer">domain</a> and use it as a type when defining table columns, e.g.

<pre><code>CREATE DOMAIN email AS TEXT CHECK (VALUE ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$');

CREATE TABLE emails (
 email email
);
</code></pre>

This way you will not need to redefine the regex every time an email containing columns is used in the database.

blocks|key|2315978|text|取自https://owasp.org/www-community/OWASP_Validation_Regex_Repository的OWASP+regexp验证库的一个更好的模式是：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2315979|2315980|+/%5Ea-zA-Z0-9_%2B&_-%2B(?:.a-zA-Z0-9_%2B&_-%2B)*@(?:a-zA-Z0-9-%2B.)%2Ba-zA-Z{2,7}$/|blockquote|2315981|2315982|2315983|但这是相当有限的。它不支持注释或引用的名称，也不支持将电子邮件发送到有效的IP地址，如name@10.99.22.4。UUCP电子邮件地址和许多其他地址也是有效的。|2315984|https://emailregex.com/建议使用以下正则表达式：|2315985|(?:[a-z0-9!#$%25&'*%2B/=?%5E_`{%7C}~-]%2B(?:\.[a-z0-9!#$%25&'*%2B/=?%5E_`{%7C}~-]%2B)*%7C"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]%7C\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)%2B[a-z0-9](?:[a-z0-9-]*[a-z0-9])?%7C\[(?:(?:25[0-5]%7C2[0-4][0-9]%7C[01]?[0-9][0-9]?)\.){3}(?:25[0-5]%7C2[0-4][0-9]%7C[01]?[0-9][0-9]?%7C[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]%7C\\[\x01-\x09\x0b\x0c\x0e-\x7f])%2B)\])|code-block|syntax|javascript|2315986|虽然我注意到这里没有锚点(%5E$)。|2315987|entityMap|0|LINK|mutability|MUTABLE|url|https://owasp.org/www-community/OWASP_Validation_Regex_Repository|1|https://emailregex.com/^0|2|1T|0|0|0|0|0|0|0|0|N|1|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|-4|5|6|7|18|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|19|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|1B|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|1C|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|1D|8|@]|9|@$A|1E|B|1F|1|1G]]|C|$]]|$1|N|3|O|5|P|7|1H|8|@]|9|@]|C|$Q|R]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1J|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]|12|$5|X|Y|Z|C|$10|13]]]]

A better pattern, taken from the OWASP regexp validation library at
<a href="https://owasp.org/www-community/OWASP_Validation_Regex_Repository" rel="nofollow noreferrer">https://owasp.org/www-community/OWASP_Validation_Regex_Repository</a> is :
<blockquote>
/^[a-zA-Z0-9_+&amp;-]+(?:.[a-zA-Z0-9_+&amp;-]+)*@(?:[a-zA-Z0-9-]+.)+[a-zA-Z]{2,7}$/
</blockquote>
But this is quite limiting. It does not support comments or quoted names, nor does it support email to an IP address such as name@[10.99.22.4] which is valid. Also valid are UUCP email addresses and a host of others.
<a href="https://emailregex.com/" rel="nofollow noreferrer">https://emailregex.com/</a> Suggest the following regexp:
<pre><code>(?:[a-z0-9!#$%&amp;'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&amp;'*+/=?^_`{|}~-]+)*|&quot;(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*&quot;)@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])
</code></pre>
Though I notice there are no anchors (^$) for this.

How can I create a constraint to use a regular expression in postgres?

How can I create a constraint to check if an email is valid in postgres?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如何在postgres中创建使用正则表达式的约束？

问如何创建约束来检查电子邮件在postgres中是否有效？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何创建约束来检查电子邮件在postgres中是否有效？EN