Check if sequence exists in Postgres (plpgsql)

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 hereEND IF;

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_nameEND IF;-- Restore the normal search path.RESET search_path;

Update: Simply testing for existence has become simpler with to_regclass() in Postgres 9.4:

SELECT to_regclass('schema_name.table_name');

But read the details:

• How to check if a table exists in a given schema

Complete function

You need to check for any table-like object that would conflict with the name, not just sequences.

This function creates a new sequence if the name is available and issues a meaningful NOTICE / WARNING / EXCEPTION respectively in other cases:

CREATE OR REPLACE FUNCTION f_create_seq(_seq text, _schema text = NULL)  RETURNS void AS$func$DECLARE   _fullname text := format('%I.%I', COALESCE(_schema,current_schema),_seq);   _relkind "char" := (SELECT c.relkind                       FROM   pg_namespace n                       JOIN   pg_class c ON c.relnamespace = n.oid                       WHERE  n.nspname = COALESCE(_schema, current_schema)                       AND    c.relname = _seq);BEGIN   IF _relkind IS NULL THEN   -- name is free      EXECUTE 'CREATE SEQUENCE ' || _fullname;      RAISE NOTICE 'New sequence % created.', _fullname;   ELSIF _relkind = 'S' THEN  -- 'S' = sequence      IF has_sequence_privilege(_fullname, 'USAGE') THEN         RAISE WARNING 'Sequence % already exists.', _fullname;      ELSE         RAISE EXCEPTION           'Sequence % already exists but you have no USAGE privilege.'         , _fullname;      END IF;   ELSE      RAISE EXCEPTION 'A(n) "%" named % already exists.'      -- Table-like objects in pg 9.4:      -- www.postgresql.org/docs/current/static/catalog-pg-class.html         , CASE _relkind WHEN 'r' THEN 'ordinary table'                         WHEN 'i' THEN 'index'                      -- WHEN 'S' THEN 'sequence'  -- impossible here                         WHEN 'v' THEN 'view'                         WHEN 'm' THEN 'materialized view'                         WHEN 'c' THEN 'composite type'                         WHEN 't' THEN 'TOAST table'                         WHEN 'f' THEN 'foreign table'                         ELSE 'unknown object' END         , _fullname;   END IF;END$func$  LANGUAGE plpgsql;COMMENT ON FUNCTION f_create_seq(text, text) IS'Create sequence if name is free.RAISE NOTICE on successful creation.RAISE WARNING if it already exists.RAISE EXCEPTION if it already exists and current user lacks USAGE privilege.RAISE EXCEPTION if object of a different kind occupies the name.$1 _seq    .. sequence name $2 _schema .. schema name (optional; default is CURRENT_SCHEMA)';

Call:

SELECT f_create_seq('myseq', 'myschema');

Or:

SELECT f_create_seq('myseq1');  -- defaults to current schema

Explain

• Also read the comment to the function at the end of the code.

• Works in Postgres 9.1+. For older versions, you only need to replace format() - which defends against SQL injection. Details:

  • SQL injection in Postgres functions vs prepared queries

• Two separate parameters allow sequences in any schema independent of the current search_path and also allow quote_ident() to do its job. quote_ident() fails with schema-qualified names - would be ambiguous.

• There is a default value for the schema parameter, so you can omit it from the call. If no schema is given, the function defaults to the current_schema. Per documentation:

  current_schema returns the name of the schema that is first in the search path (or a null value if the search path is empty). This is the schema that will be used for any tables or other named objects that are created without specifying a target schema.

• List of types for pgclass.relkind in the manual.

• PostgreSQL error codes.