Check if sequence exists in Postgres (plpgsql)

Question

I'm trying to test, within a stored procedure, whether a sequence already exists.

IF EXISTS SEQUENCE seq_name     RAISE EXCEPTION 'sequence % already exists!', seq_name END IF; 

I have tried several variations of the snippet above without luck. I must be giving Google the wrong terms because I can't seem to find anything on the topic. Any help is appreciated!

Answer 1

You should be able query the pg_class table to see if the relname exists.

IF EXISTS (SELECT 0 FROM pg_class where relname = '<my sequence name here>' ) THEN   --stuff here END IF; 

Answer 2

The answer from @rfusca works if you're sure that the name could only be valid for a sequence (i.e., you're confident that it would not be use for an ordinary table, index, view, composite type, TOAST table, or foreign table), and you're not concerned about multiple schemas. In other words, it works for most common cases, but it's not entirely rigorous.

If you want to test whether a sequence by that name exists in a particular schema, this should work:

-- Clear the search path so that the regclass of the sequence -- will be schema-qualified. SET search_path = ''; -- Do your conditional code. IF EXISTS (SELECT * FROM pg_class              WHERE relkind = 'S'                AND oid::regclass::text = 'public.' || quote_ident(seq_name))   THEN     RAISE EXCEPTION 'sequence public.% already exists!', seq_name END IF; -- Restore the normal search path. RESET search_path;