postgresql 如何在 Sequelize Postgres 中使用小写函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34754335/
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
How to use Lowercase function in Sequelize Postgres
提问by spyalert01
I am trying to use the lowercase function to do string searching in Sequelize. I manage to do it using the ilike. My question is how to use the lowercase function in this scenario?
我正在尝试使用小写函数在 Sequelize 中进行字符串搜索。我设法使用 ilike 来做到这一点。我的问题是如何在这种情况下使用小写函数？
The findAllusing ilike is as following:
在findAll使用ILIKE是如下：
Db.models.Person.findAll(where: {firstName: {$ilike: `somename`}});
How do I change it to lower(firstname) = lower('somename');
我如何将其更改为 lower(firstname) = lower('somename');
回答by Lee Benson
PostgreSQL generally uses case-sensitive collations. This means data that appears in each column is compared literally against your query.
PostgreSQL 通常使用区分大小写的排序规则。这意味着每列中出现的数据会与您的查询逐字比较。
You have several options:
您有多种选择：
1. Follow Ben's answer, and wrap your wrap columns and database in a sequelize.fn('lower')call.
1. 按照 Ben 的回答，在sequelize.fn('lower')通话中包装您的包装列和数据库。
Pros: No database changes.
优点：无需更改数据库。
Cons: You need to remember to use it for every query. Foregoes indexes (unless you've already created a functional index) and scans tables sequentially, resulting in slower look-ups with large tables. Quite verbose code.
缺点：您需要记住对每个查询使用它。放弃索引（除非您已经创建了功能索引）并按顺序扫描表，从而导致大表的查找速度变慢。相当冗长的代码。
2. Use ILIKE, to case-insensitively match a pattern
2. 使用 ILIKE，不区分大小写匹配模式
To find the name exactly:
要准确查找名称：
Db.models.Person.findAll(where: {firstName: {$iLike: 'name'}});
Db.models.Person.findAll(where: {firstName: {$iLike: 'name'}});
To find a fragment, which may be contained within arbitrary characters:
要查找可能包含在任意字符中的片段：
Db.models.Person.findAll(where: {firstName: {$iLike: '%name%'}});
Db.models.Person.findAll(where: {firstName: {$iLike: '%name%'}});
Pros: Easy to remember. No Sequelize function wrappers - it's a built-in operator, so syntax is neater. No special indexes or database changes required.
优点：容易记住。没有 Sequelize 函数包装器 - 它是一个内置运算符，因此语法更简洁。不需要特殊的索引或数据库更改。
Cons: Slow, unless you start messing with extensions like pg_trgm
3. Define your text columns with the citexttype, which implicitly compares lowercase
3. 用citext类型定义你的文本列，隐式比较小写
Defining your column types as 'citext' (instead of textor character varying) has the same practical effect of turning this:
将您的列类型定义为 'citext'（而不是text或character varying）具有与此相同的实际效果：
select * from people where name = 'DAVID'
select * from people where name = 'DAVID'
to this...
到这...
select * from people where LOWER(name) = LOWER('DAVID')
select * from people where LOWER(name) = LOWER('DAVID')
The PostgreSQL documentation shows this as an example of how to create your table with the citext type:
PostgreSQL 文档将此显示为如何使用 citext 类型创建表的示例：
CREATE TABLE users (
nick CITEXT PRIMARY KEY,
pass TEXT NOT NULL
);
INSERT INTO users VALUES ( 'larry', md5(random()::text) );
INSERT INTO users VALUES ( 'Tom', md5(random()::text) );
INSERT INTO users VALUES ( 'Damian', md5(random()::text) );
INSERT INTO users VALUES ( 'NEAL', md5(random()::text) );
INSERT INTO users VALUES ( 'Bj?rn', md5(random()::text) );
SELECT * FROM users WHERE nick = 'Larry';
TL;DR basically swap out your "text" columns for "citext".
TL;DR 基本上将您的“text”列换成“citext”。
The citext module comes bundled with PostgreSQL 8.4, so there's no need to install any extensions. But you do need to enable it on each database you use it with the following SQL:
citext 模块与 PostgreSQL 8.4 捆绑在一起，因此无需安装任何扩展。但是您确实需要在使用以下 SQL 的每个数据库上启用它：
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
And then in your Sequelize definitions, define a typeattribute:
然后在您的 Sequelize 定义中，定义一个type属性：
// Assuming `Conn` is a new Sequelize instance
const Person = Conn.define('person', {
firstName: {
allowNull: false,
type: 'citext' // <-- this is the only change
}
});
Then your searches against that column will alwaysbe case-insensitive with regular where =queries
那么您对该列的搜索将始终对常规where =查询不区分大小写
Pros: No need to wrap your queries in ugly sequelize.fncalls. No need to remember to explicitly lowercase. Locale aware, so works across all character sets.
优点：无需将您的查询包装在丑陋的sequelize.fn调用中。无需记住显式小写。语言环境感知，因此适用于所有字符集。
Cons: You need to remember to use it in your Sequelize definitions when first defining your table. Always activated - you need to know that you'll want to do case insensitive searching when defining your tables.
缺点：您需要记住在第一次定义表时在 Sequelize 定义中使用它。始终激活 - 您需要知道在定义表时要进行不区分大小写的搜索。
回答by Ben Polge
You can use native functions in the where clause:
您可以在 where 子句中使用本机函数：
Db.models.Person.findAll({
where: sequelize.where(
sequelize.fn('lower', sequelize.col('firstname')),
sequelize.fn('lower', 'somename')
)
});
which would translate to
这将转化为
select * from person where lower(firstname) = lower('somename');
