Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 999
Larry Avatar asked Oct 02 '11 06:10

Larry


1 Answers

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$$; 
like image 193
bluish Avatar answered Oct 08 '22 07:10

bluish