主页 / dba / 问题 / 201837
Accepted
Amanda
Asked: 2018-03-21 17:13:21 +0800 CST

ORDER BY 给出错误：函数数组位置（文本 []，字符变化）不存在

  • 772

我在 Postgres 数据库中有一个非常基本的分类列，当前存储为 VARCHAR。我可以选择每个计数：

我虽然添加一个ORDER BY array_position()会做到这一点：

SELECT color, count(*)
FROM research
GROUP BY color 
ORDER BY array_position(ARRAY['Red','Orange','Yellow','Green','Blue'], color);

但我看到一个类型错误：

ERROR: function array_position(text[], character varying) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 98

我需要转换什么color才能使用该array_position功能来订购它？

postgresql order-by

1 个回答

  1. Best Answer

    样本数据

    你有一张像这样的桌子，

    CREATE TABLE research(colors)
  AS VALUES ('Blue'), ('Orange'), ('Yellow');

    ENUM类型

    您有一个枚举的颜色列表。所以这里最简单的事情就是使用ENUM 类型

    CREATE TYPE colors AS ENUM ('Red','Orange','Yellow','Green','Blue');

    然后

    ALTER TABLE research
  ALTER COLUMN colors      -- myColorsColumn
  SET DATA TYPE colors
  USING (colors::colors);  -- myColorsColumn::NewType

    现在它更快、更高效、更清洁。更多的胜利。更多欢乐。等ENUM在内部存储为 4 字节。

    ORDER BY colors

    就是你所需要的。

    数组排序

    但是，您拥有上帝赋予的权利，可以像对待任何其他不支持 ENUM 类型的数据库一样对待它，

    ORDER BY array_position(ARRAY['Red','Orange','Yellow','Green','Blue'], color);

    我认为这很好，但我相信这里的实现有一个限制，

    ARRAY['foo', 'bar', 'baz']

    本质上是

    ARRAY['foo', 'bar', 'baz']::text

    这意味着您需要（速度无关紧要，因为两个选项执行相同）

    1. 制作color原生的专栏text。这可以在调用中完成，或者您可以实际修改表和应该。在 PostgreSQL 中，没有什么是varchar没有限制的（因为这只是文本的并行类型），很少有东西应该varchar有限制（因为没有优势）

      color::text            -- PostgreSQL sexy sexy cast
CAST(color AS TEXT))   -- ANSI SQL standardized vanilla and boring

    2. 或者，您可以将数组本身构造为类型varchar[]

      array_position(ARRAY['Red','Orange','Yellow','Green','Blue']::varchar[], color);

    补充说明

    • 在 PostgreSQL 中，我们不会使用varcharfor colors。即使您坚持不使用ENUM此处（尽管我愿意），那也应该是text

    • 这array_position是一个速记，我希望它比类似的操作要慢得多

      CASE
  WHEN color='Red' THEN 1::smallint
  WHEN color='Orange' THEN 2::smallint
  WHEN color='Yellow' THEN 3::smallint
  ... etc
END;
    • 12

相关问题