sql - PSQL unnest 函数未按预期工作

Question

我尝试了 this post 中的解决方案但我仍然遇到错误。

查询:

SELECT unnest(team)
FROM table_of_teams
WHERE team LIKE '%akg%';

错误:

ERROR:  operator does not exist: character varying[] ~~ unknown
LINE 5: WHERE team LIKE '%akg%'
                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

表结构:

                            Table "public.table_of_teams"
       Column       |            Type             |                   Modifiers                     
--------------------+-----------------------------+-------------------------------------------------
 teamid             | integer                     | not null default nextval('team_seq'::regclass)
 index              | integer                     | 
 name               | character varying           | 
 grouping           | character varying           | 
 hour_of_day        | integer[]                   | 
 day_of_week        | integer[]                   | 
 team               | character varying[]         | 

Answer 1

如果我理解正确并且您想提取满足给定条件的团队，您可以将 unnest 调用放在子查询中并在周围查询中应用条件:

SELECT single_team
FROM   (SELECT unnest(team) single_team
        FROM table_of_teams) t
WHERE single_team LIKE '%akg%';