CODEWITHSUNDEEP
postgresqluser-defined-types
Larry
Larry

Reputation: 11929

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.

Upvotes: 152

Views: 134354

Answers (15)

Alexi Theodore
Alexi Theodore

Reputation: 1677

If you need to do something like this done in strict psql (i.e. no procedure blocks), you can also do:

SELECT NOT exists(SELECT * FROM pg_type WHERE typname = 'your_type') as is_needed;
\gset
\if :is_needed
    CREATE TYPE your_type AS ...;
\endif

Upvotes: 0

Miklos Krivan
Miklos Krivan

Reputation: 1802

You can find the answer here Check if a custom type is installed in a schema

SELECT 1
FROM pg_type 
WHERE typname = 'your_custom_type'
  and typnamespace = 'your_schema_containing_custom_type'::regnamespace

Thanks the answer to @user330315

Upvotes: 0

Cromax
Cromax

Reputation: 2052

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 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 an error of 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.

Upvotes: 20

Vladimir
Vladimir

Reputation: 1

Continue with bluish code, we also need to check if DB has such a type in current schema. Because current code will not create type if db has same type in any of db schemas. So full universal code will look like this:

$$
    BEGIN
        IF NOT EXISTS(select 
                      from pg_type
                      WHERE typname = 'YOUR_ENUM_NAME'
                        AND typnamespace in
                            (SELECT oid FROM pg_catalog.pg_namespace where nspname = "current_schema"())) THEN
            CREATE TYPE YOUR_ENUM_NAME AS ENUM (....list of values ....);
        END IF;
    END
$$;```

Upvotes: 0

Hans Brende
Hans Brende

Reputation: 8577

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

Upvotes: 11

András Pál
András Pál

Reputation: 96

You should try this:

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

Upvotes: 0

Julien
Julien

Reputation: 5729

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

Upvotes: 2

Panchove
Panchove

Reputation: 571

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

Upvotes: 1

Zied Feki
Zied Feki

Reputation: 872

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'')');

Upvotes: 3

rog
rog

Reputation: 6670

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.

Upvotes: 146

mu is too short
mu is too short

Reputation: 434685

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.

Upvotes: 35

Nulik
Nulik

Reputation: 7360

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.

Upvotes: 59

bluish
bluish

Reputation: 27320

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

Upvotes: 141

bsb
bsb

Reputation: 1937

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

Upvotes: 1

Eloy Zuniga Jr.
Eloy Zuniga Jr.

Reputation: 101

-- 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();
-----------------------------------

Upvotes: 6

Related Questions