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.

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$$;

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.

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.

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.

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.

-- All of this to create a type if it does not exist
CREATE OR REPLACE FUNCTION create_abc_type() RETURNS integer AS $$
DECLARE v_exists INTEGER;

BEGIN
    SELECT into v_exists (SELECT 1 FROM pg_type WHERE typname = 'abc');
    IF v_exists IS NULL THEN
        CREATE TYPE abc AS ENUM ('height', 'weight', 'distance');
    END IF;
    RETURN v_exists;
END;
$$ LANGUAGE plpgsql;

-- Call the function you just created
SELECT create_abc_type();

-- Remove the function you just created
DROP function create_abc_type();
-----------------------------------

I'm trying to do the same thing, ensure a type exists.

I started psql with the --echo-hidden (-E) option and entered \dT:

$ psql -E
psql (9.1.9)
testdb=> \dT
********* QUERY **********
SELECT n.nspname as "Schema",
  pg_catalog.format_type(t.oid, NULL) AS "Name",
  pg_catalog.obj_description(t.oid, 'pg_type') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;
**************************
 List of data types
 Schema |       Name       | Description 
--------+------------------+-------------
 public | errmsg_agg_state | 
(1 row)

If you are using schemas and search_path (I am) then you'll probably need to keep the pg_catalog.pg_type_is_visible(t.oid) check. I don't know what all the conditions in the WHERE are doing, but they didn't seem relevant to my case. Currently using:

SELECT 1 FROM pg_catalog.pg_type as t
   WHERE typname = 'mytype' AND pg_catalog.pg_type_is_visible(t.oid);

A more generic solution

CREATE OR REPLACE FUNCTION create_type(name text, _type text) RETURNS 
integer AS $$
DECLARE v_exists INTEGER;

BEGIN
    SELECT into v_exists (SELECT 1 FROM pg_type WHERE typname = name);
    IF v_exists IS NULL THEN
    		EXECUTE format('CREATE TYPE %I AS %s', name, _type);
    END IF;
    RETURN v_exists;
END;
$$ LANGUAGE plpgsql;

and then you can call it like this:

select create_type('lwm2m_instancetype', 'enum (''single'',''multiple'')');

This plays well with schemas, and avoids exception handling:

DO $$
BEGIN
    IF NOT EXISTS (
      SELECT 1 FROM pg_type t
      LEFT JOIN pg_namespace p ON t.typnamespace=p.oid
      WHERE t.typname='my_type' AND p.nspname='my_schema'
    ) THEN
        CREATE TYPE my_schema.my_type AS (/* fields go here */);
    END IF;
END
$$;

Another alternative

WITH namespace AS(
	SELECT oid 
		FROM pg_namespace 
		WHERE nspname = 'my_schema'
),
type_name AS (
	SELECT 1 type_exist  
		FROM pg_type 
		WHERE typname = 'my_type' AND typnamespace = (SELECT * FROM namespace)
)
SELECT EXISTS (SELECT * FROM type_name);

You should try this:

SELECT * from pg_enum WHERE enumlabel='WHAT YOU WANT';

Inspired by @Cromax's answer, here's an alternative solution using the system catalog information function to_regtype that avoids the extra overhead of an exception clause but still checks the correct schema for the type's existence:

DO $$ BEGIN
    IF to_regtype('my_schema.abc') IS NULL THEN
        CREATE TYPE my_schema.abc ... ;
    END IF;
END $$;

In the case of the default public schema being used, it would look like:

DO $$ BEGIN
    IF to_regtype('abc') IS NULL THEN
        CREATE TYPE abc ... ;
    END IF;
END $$;