Check if a user-defined type already exists in PostgreSQL

Say I have created some user-defined types in the DB,

i.e. CREATE TYPE abc ...

Is it then possible to determine if the user-defined type exists or not? Perhaps, using any of the postgres information tables?

The main reason for this is since PostgreSQL does not seem to support CREATE OR REPLACE TYPE ..., and if a certain type gets created more than once, I want to be able to drop the existing one first, then re-load the new one.

Solution 1:

I add here the complete solution for creating types in a simple script, without the need of creating a function just for this purpose.

--create types
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_type') THEN
        CREATE TYPE my_type AS
        (
            --my fields here...
        );
    END IF;
    --more types here...
END$$;

Solution 2:

The simplest solution I've found so far that copes with schemas, inspired by @Cromax's answer, is this:

DO $$ BEGIN
    CREATE TYPE my_type AS (/* fields go here */);
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

Just what you might expect really - we just wrap the CREATE TYPE statement in an exception handler so it doesn't abort the current transaction.

Solution 3:

Indeed, Postgres does not have CREATE OR REPLACE functionality for types. So the best approach is to drop it:

DROP TYPE IF EXISTS YOUR_TYPE;
CREATE TYPE YOUR_TYPE AS (
    id      integer,
    field   varchar
);

Simple solution is always the best one.

Solution 4:

You can look in the pg_type table:

select exists (select 1 from pg_type where typname = 'abc');

If that is true then abc exists.

Solution 5:

To solve @rog's dilemma to @bluish's answer it could be more appropriate to make use of regtype data type. Consider this:

DO $$ BEGIN
    PERFORM 'my_schema.my_type'::regtype;
EXCEPTION
    WHEN undefined_object THEN
        CREATE TYPE my_schema.my_type AS (/* fields go here */);
END $$;

PERFORM clause is like SELECT, but it discards results, so basically we're checking if it is possible to cast 'my_schema.my_type' (or just 'my_type' if you don't care to be schema specific) to actual registered type. If the type does exist, then nothing "wrong" will happen and because of RETURN whole block will end—no changes, since the type my_type is already there. But if the cast is not possible, then there will be thrown error code 42704 which has label of undefined_object. So in the next lines we try to catch that error and if that happens, we simply create our new data type.