postgres array 语法

https://www.postgresqltutorial.com/postgresql-array/

 

https://www.postgresql.org/docs/9.1/arrays.html

 

展开列表：变成多行

SELECT group_name, unnest(alias) as alias_name FROM user_table

 

postgres 数组元素不区分大小写匹配

g = db.session.execute(
    '''SELECT *
    FROM (select *, unnest("alias") as alias_name from table_name) as table_name
    WHERE lower(group_name) = lower(:group_name) or lower(alias_name) = lower(:group_name)''',
    params={"group_name": group_name}, bind=db.get_engine(current_app, bind='database_name')).fetchone()

g = db.session.query(Class对象模型).from_statement(text(
    '''SELECT * FROM (select *, unnest("alias") as alias_name from table_name) as table_name WHERE lower(group_name) = lower(:group_name) or lower(alias_name) = lower(:group_name)''')). \
    params(group_name=group_name).first()

 

flask_sqlalchemy 、sqlalchemy 执行原生sql：

http://www.itspire.cn/article/23.html

http://www.manongjc.com/detail/13-kjxbkwwygyurhso.html 

 

对列表内的元素执行 like查询

SELECT * FROM (
SELECT
group_name,
unnest(alias) as alias_name
FROM
user_table
) as ag WHERE alias_name like '%ou%'