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.

81983 次浏览
小开

您可以查看 pg_type表:

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

如果这是真的，那么 abc存在。

小开

我也在做同样的事，确保类型的存在。

我使用 --echo-hidden(-E)选项启动 psql，然后输入 \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)

如果您正在使用 schema 和 search _ path (我是) ，那么您可能需要保持 pg_catalog.pg_type_is_visible(t.oid)检查。我不知道 WHERE 中的所有条件都在做什么，但它们似乎与我的情况无关。目前使用:

SELECT 1 FROM pg_catalog.pg_type as t
WHERE typname = 'mytype' AND pg_catalog.pg_type_is_visible(t.oid);
小开 
-- 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();
-----------------------------------
小开

我在这里添加了在简单脚本中创建类型的完整解决方案，而不需要为此目的创建函数。

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

实际上，Postgres 并没有针对类型的 CREATE OR REPLACE功能，因此最好的方法是删除它:

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

简单的解决方案总是最好的。

小开

为了解决@rog 对@bluish 的回答所面临的困境，使用 regtype数据类型可能更为合适。想想这个:

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子句类似于 SELECT，但是它丢弃了结果，所以基本上我们是在检查是否可以将 'my_schema.my_type'(或者只是 'my_type'，如果您不关心特定于模式的话)转换为实际的注册类型。如果该类型确实存在，那么就不会发生任何“错误”，而且由于 RETURN的存在，整个代码块将不会结束ーー不会发生任何更改，因为 my_type类型已经存在。但是如果强制转换是不可能的，那么将抛出具有 undefined_object标签的错误代码 42704。因此，在接下来的行中，我们尝试捕捉这个错误，如果出现这种情况，我们只需创建新的数据类型。

小开

受到@Cromax 回答的启发，我迄今为止找到的处理模式的最简单的解决方案是:

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

正如您所期望的那样——我们只是将 CREATETYPE 语句包装在一个异常处理程序中，这样它就不会中止当前事务。

小开

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;

然后你可以这样称呼它:

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

小开

另一个选择

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

这可以很好地处理模式，并避免异常处理:

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

你应该试试这个:

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 异常子句的额外开销 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 $$;

在使用默认 public模式的情况下，它看起来像:

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

继续使用蓝色代码，我们还需要检查 DB 在当前模式中是否有这样的类型。因为如果 db 在任何 db 架构中具有相同的类型，当前代码将不会创建类型。所以完整的通用代码应该是这样的:

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

提交答案