PostgreSQL中CITEXT扩展提示不存在又已存在的问题解决

我通过以下命令将Supabase数据库导出到本地机器：

pg_dump -Fc -U postgres "[DATABASE URL]" > database.dump
pg_restore -U postgres -d dump_test database.dump

当尝试创建使用CITEXT扩展的新表时，出现错误：

CREATE TABLE example_table (
    table_id BIGSERIAL PRIMARY KEY NOT NULL,
    table_column CITEXT
);

ERROR:  type "citext" does not exist

自行添加CITEXT扩展时，提示已存在：

CREATE EXTENSION IF NOT EXISTS CITEXT;

NOTICE:  extension "citext" already exists, skipping

当前搜索路径为：

"$user", public

执行\dx命令返回结果：

版本信息

• Supabase：PostgreSQL 14.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1~20.04) 10.3.0, 64-bit
• 本地PostgreSQL：14.1, compiled by Visual C++ build 1914, 64-bit
• pg_dump/pg_restore：14.1

请问该现象的原因是什么？如何创建使用该扩展的表？

CITEXT扩展确实已经安装，但它被部署在extensions schema下，而你的当前搜索路径仅包含"$user"和public，PostgreSQL无法找到extensions schema中的citext类型，因此抛出错误。

有两种可行的解决方式：

方式一：修改搜索路径，添加extensions schema #

执行以下SQL，将extensions加入当前会话的搜索路径：

SET search_path TO "$user", public, extensions;

如果需要让该设置对当前用户永久生效，可执行：

ALTER USER your_username SET search_path TO "$user", public, extensions;

完成后即可直接使用CITEXT类型创建表。

方式二：创建表时指定类型的schema前缀 #

若不想修改搜索路径，创建表时直接写明citext类型的完整schema路径：

CREATE TABLE example_table (
    table_id BIGSERIAL PRIMARY KEY NOT NULL,
    table_column extensions.citext
);

内容的提问来源于stack exchange，提问作者brainf___enthusiast