Question 249787 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Copy table with custom type from one schema to another

I want to copy data from one table in schema "test" to another table in schema "public". Both tables are identical, but the field "type" is a custom type named "item_type". The types are defined within their schema. Both types are also the same, except they belong to two different schemas.

What I tried:

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;

However I get the following error:

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

How can I solve this?

Edit:

The custom type looks like this:

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

2

Answers


  1. 0

    Because test.item_type is a custom type, the PostgreSQL doesn’t have knowledge of how it should convert it to a different custom type public.item_type.

    You must provide instructions for the PostgreSQL how to convert from one type to another.

    One way is to create a function for that:

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

    Later you can use it to provide a valid value for the 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%';

    To be able to simply cast, you must define a new CAST (see the Docs ).

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

    That way define how the test.item_type should be converted to public.item_type.

    Now the query

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

    runs without errors.

    Feel free to explore full test case at db<>fiddle

    Lastly I’d like to suggest of not using private.item_type at all. Why not create a table private.item with type of public.item_type?.. It seems unnecessary…

    Login or Signup to reply.
  2. 0

    Some additions to Julius‘ answer:
    it seems most convenient if you just use the intermediate cast directly in your insert, whenever you deal with directly mappable types:

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

    Technically, there’s no difference if you do that or (re)define all your casts before copying the data over. If that’s a one-off job, those definitions only add extra steps.

    If you do want to set them up, you can actually get away without (explicit) casting entirely, and save some (re)definitions thanks to a single, polymorphic function: demo

    CREATE FUNCTION to_public_item_type_casts(anyelement) RETURNS public.item_type
LANGUAGE SQL AS $f$ SELECT $1::text::public.item_type $f$;

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

--if another schema clone comes in, you don't need a new function, only another cast
CREATE CAST (some_new_schema.item_type AS public.item_type) 
WITH FUNCTION to_public_item_type_casts AS IMPLICIT;

    AS IMPLICIT lets PostgreSQL detect and use the cast on its own, letting you simply

    INSERT INTO public.item
SELECT * FROM test.item
WHERE status like 'OK%'
ON CONFLICT DO NOTHING;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search