用于检查非 ascii 字符的 Postgresql 约束

Question

我有一个编码为“UTF8”的 Postgresql 9.3 数据库。但是，数据库中有一列除了 ASCII 外永远不应该包含任何内容。如果非 ascii 进入那里，它会导致另一个我无法控制的系统出现问题。因此，我想为该列添加一个约束。注意:我已经有一个 BEFORE INSERT 触发器 - 所以这可能是进行检查的好地方。

在 PostgreSQL 中完成此任务的最佳方法是什么？

Answer 1

为此，您可以将 ASCII 定义为 ordinal 1 to 127，因此以下查询将识别具有“非 ascii”值的字符串:

SELECT exists(SELECT 1 from regexp_split_to_table('abcdéfg','') x where ascii(x) not between 1 and 127);

但它不太可能非常高效，并且子查询的使用会迫使您在触发器而不是 CHECK 约束中执行此操作。

相反，我会使用正则表达式。如果您想要所有可打印字符，那么您可以在检查约束中使用一个范围，例如:

CHECK (my_column ~ '^[ -~]*$')

this will match everything from the space to the tilde , 这是可打印的 ASCII 范围。

如果您想要所有 ASCII，可打印和不可打印，您可以 use byte escapes :

CHECK (my_column ~ '^[\x00-\x7F]*$')

---

最严格正确的方法是 convert_to(my_string, 'ascii') 并在失败时引发异常...但是 PostgreSQL 不提供 ascii(即 7 位)编码，因此这种方法是不可能的。