Check if sequence exists in Postgres (plpgsql)

30

votes

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!

postgresqlstored-proceduresplpgsql

26

votes

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;

19

votes

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;

15

votes

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.

9

votes

How about using the information schema :

SELECT COUNT(*) 
FROM information_schema.sequences 
WHERE sequence_schema=? AND sequence_name=?

1

votes

select relname, relnamespace
from pg_class join pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
where n.nspname='metastore_1' and relname='updater_state_id_seq';

Result:

       relname        | relnamespace 
-------------------------------------
 updater_state_id_seq |        32898

This query can check existence of a sequence inside a schema.

1

votes

I'm not sure about the actual intent why the presence of the sequence must be checked. An alternative if the goal is to check if a sequence exists before creating it, the IF NOT EXISTS condition in PostgreSQL can be used:

CREATE SEQUENCE IF NOT EXISTS 'name'

See https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

Check if sequence exists in Postgres (plpgsql)

6 Answers

Complete function

Explain