Postgresql 将具有自定义类型的表从一种模式复制到另一种模式

发布于09月21日

我想将数据从模式"test"中的一个表复制到模式"PUBLIC"中的另一个表.这两个表是相同的，但"type"字段是名为"Item_type"的自定义类型.这些类型在它们的架构中定义.这两种类型也是相同的，只是它们属于两个不同的架构.

我try 了什么:

INSERT INTO public.item
SELECT itemid, collectionid, signature, CAST(type AS public.item_type), status
FROM test.item
WHERE status like 'OK%'
ON CONFLICT DO NOTHING;

但是，我得到以下错误:

ERROR:  cannot cast type test.item_type to item_type
LINE 2: SELECT itemid, collectionid, signature, CAST(type AS pu...

我怎么才能解决这个问题？

编辑:

自定义类型如下所示:

CREATE TYPE test.item_type AS ENUM
    ('image', 'audio', 'video', 'application', 'text');

推荐答案

因为test.item_type是一个定制类型，所以PostgreSQL不知道它应该如何将它转换为不同的定制类型public.item_type.

您必须为PostgreSQL提供如何从一种类型转换为另一种类型的说明.

一种方法是为此创建一个函数:

CREATE FUNCTION public.test_item_type_2_public(test.item_type) 
  returns public.item_type
  language sql
AS $$
SELECT $1::text::public.item_type;
$$;

稍后，您可以使用它为public.item提供有效值:

INSERT INTO public.item
SELECT itemid, collectionid, signature, public.test_item_type_2_public(i.type), status
FROM test.item i
WHERE status like 'OK%';

为了能够简单地进行强制转换，您必须定义一个新的强制转换(参见Docs).

CREATE CAST (test.item_type AS public.item_type)
    WITH FUNCTION public.test_item_type_2_public;

这样就定义了test.item_type应该如何转换为public.item_type.

现在的查询是

INSERT INTO public.item
SELECT itemid, collectionid, signature, CAST(type AS public.item_type), status
FROM test.item
WHERE status like 'OK%';

运行时没有错误.

在db<>fiddle上自由探索完整的测试用例

最后，我想建议不要使用private.item_type.为什么不创建一个类型为public.item_type的表private.item？这似乎没有必要...

Postgresql相关问答推荐

Redis作为postgreSQL嵌套数据的缓存

Pogresql性能中的枚举与文本数据类型

在postgres中撤销Select后，RLS停止工作

Power BI + Postgres：只读用户

PostgreSQL - 列出模式中的所有唯一约束

如何计算过滤器中的百分比

订阅标签保存在哪个表中？

Postgres - 如何加入两列？

如何展平也在关系中使用的区分大小写的列

将数组插入 Postgresql 数据库

我可以在 Ruby on Rails 上编写 PostgreSQL 函数吗？

Django + Postgres + 大时间序列

从 Postgres 字段中的多个值中进行 Select

将 PostgreSQL 配置为仅适用于 LOCALHOST 或指定的 ip + 端口

PostgreSQL如何连接间隔值'2天'

避免 created_at 和 updated_at 由 sequelize 自动生成

Postgres 外键on update和on delete选项如何工作？

将属性添加到 Sequelize FindOne 返回的对象

在 PostgreSQL 中的表上禁用 DELETE？

无法在 postgresql hibernate 中使用名为user的表

实用课程推荐