Check if a user-defined type already exists in PostgreSQL

postgres create type
type in postgresql
postgres temp type
create type in schema postgres
create type array postgresql
create type if not exists mssql
alter custom type postgres
update type enum postgres

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

Documentation: 9.1: DROP TYPE, DROP TYPE removes a user-defined data type. Only the owner of a type can remove it. Parameters. IF EXISTS. Do not throw an error if the type  I have a user-defined table type. I want to check it's existence before editing in a patch using OBJECT_ID(name, type) function. What type from the enumeration should be passed for user-defined ta


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.

Documentation: 9.5: CREATE TYPE, The user who defines a type becomes its owner. an associated b-tree operator class (to determine the ordering of values for the range type). Before PostgreSQL version 8.2, the shell-type creation syntax CREATE TYPE name did not exist. This command is similar to the corresponding command in the SQL standard, apart from the IF EXISTS option, which is a PostgreSQL extension. But note that much of the CREATE TYPE command and the data type extension mechanisms in PostgreSQL differ from the SQL standard.


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.

Documentation: 9.5: User-defined Types, A user-defined type must always have input and output functions. These functions determine how the type appears in strings (for input by the user and output to the user) and Now we can define the I/O functions: Once the data type exists, we can declare additional functions to provide useful operations on the data type. On 06/22/10 11:16 PM, kaifeng.zhu wrote: > Hi there, > How can I know whether an index already exists? > I have googled for hours and cannot found the solution


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.

Documentation: 9.5: ALTER TYPE, ALTER TYPE changes the definition of an existing type. If IF NOT EXISTS is specified, it is not an error if the type already contains the new value: a notice is issued but no other action is taken. The user name of the new owner of the type​. Check if table or View exists. I am developing an application where I will have a default table and/or. view for a select statement for my application. If a client has a special. requirement I would like to have a alternate table of the same name in a. different schema with the change structure.


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.

Documentation: 7.3: CREATE VIEW, This error occurs if the view specified already exists in the database. WARNING: Attribute 'column' has an unknown type. The view will be created having a column with an unknown type if you do not on the view will be checked to ensure data satisfy the view-defining condition. If they CREATE USER, Up, DEALLOCATE  Sent from the PostgreSQL - general mailing list archive at Nabble.com. Responses Re: Function already exists with same argument types at 2012-06-13 02:15:59 from Craig Ringer


Display user-defined types and their details, I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined  Fastest check if row exists in PostgreSQL. I have a bunch of rows that I need to insert into table, but these inserts are always done in batches. So I want to check if a single row from the batch exists in the table because then I know they all were inserted. So its not a primary key check, but shouldn't matter too much.


Create index if it does not exist, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema. If you do not care about the name of the index, have Postgres auto-name it: A very simple way to test is to cast the schema-qualified name to regclass : DO $$ BEGIN IF NOT EXISTS ( SELECT FROM pg_class c JOIN  The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is,


PostgreSQL EXISTS By Practical Examples, This tutorial shows you how to use the PostgreSQL EXISTS to test for the existence of rows in the subquery. The EXISTS operator is used to test for existence of rows in a subquery. NOT EXISTS is opposite to EXISTS , meaning that if the subquery returns no row, NOT EXISTS returns true. If the PostgreSQL Data Types. This PostgreSQL tutorial explains how to use the PostgreSQL EXISTS condition with syntax and examples. The PostgreSQL EXISTS condition is used in combination with a subquery and is considered to be met if the subquery returns at least one row.