Postgresql：在插入列时（或之前）自动小写文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9046825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql: auto lowercase text while (or before) inserting to a column
提问by user1144616
I want to achieve case insensitive uniqueness in a varchar column. But, there is no case insensitive text data type in Postgres. Since original case of text is not important, it will be a good idea to convert all to lowercase/uppercase before inserting in a column with UNIQUE constraint. Also, it will require one INDEX for quick search.
我想在 varchar 列中实现不区分大小写的唯一性。但是，Postgres 中没有不区分大小写的文本数据类型。由于文本的原始大小写并不重要，因此在插入具有 UNIQUE 约束的列之前将所有内容转换为小写/大写将是一个好主意。此外，它还需要一个 INDEX 以进行快速搜索。
Is there any way in Postgres to manipulate data before insertion?
Postgres 有没有办法在插入前操作数据？
I looked at this other question: How to automatically convert a MySQL column to lowercase. It suggests using triggers on insert/update to lowercase text or to use views with lowercased text. But, none of the suggested methods ensure uniqueness.
我看了另一个问题：如何自动将 MySQL 列转换为小写。它建议在插入/更新小写文本时使用触发器或使用带有小写文本的视图。但是，建议的方法都不能确保唯一性。
Also, since this data will be read/written by various applications, lowercasing data in every individual application is not a good idea.
此外，由于这些数据将由各种应用程序读取/写入，因此在每个单独的应用程序中小写数据并不是一个好主意。
回答by a_horse_with_no_name
You don't need a case-insensitive data type (although there is one)
您不需要不区分大小写的数据类型（尽管有一个）
CREATE UNIQUE INDEX idx_lower_unique
ON your_table (lower(the_column));
That way you don't even have to mess around with the original data.
这样你甚至不必弄乱原始数据。
回答by Craig R. Skinner
ALTER TABLE your_table
ADD CONSTRAINT your_table_the_column_lowercase_ck
CHECK (the_column = lower(the_column));
从手册：
The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly.
使用索引来强制唯一约束可以被视为不应直接访问的实现细节。
