Check if sequence exists in Postgres (plpgsql)
Solution 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;
Solution 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;
Solution 3
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:
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: Two separate parameters allow sequences in any schema independent of the current
search_path
and also allowquote_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.
Solution 4
How about using the information schema :
SELECT COUNT(*)
FROM information_schema.sequences
WHERE sequence_schema=? AND sequence_name=?
Solution 5
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.
Ilia Choly
Favourite Languages: Go TypeScript Python C Lisp Interests: Networking Distributed/Parallel Computing Compilers Security Social Twitter Facebook Github
Updated on July 09, 2022Comments
-
Ilia Choly almost 2 years
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!
-
Evgeny over 11 yearswhat is oid::regclass::text? Also in my case relkind was "S" - upper case, however for the tables - 'r' with lower case.
-
Evgeny over 11 yearsAlso after testing I find that quote_ident does not work for sequence names that do not exist.
-
kgrittn over 11 years@Evgeny: Regarding the cast to
regclass
and then totext
, see this page for info onregclass
postgresql.org/docs/9.2/interactive/datatype-oid.html --text
is just a character string. Sorry I got the relkind comparison wrong -- it is indeed a capital S which is needed. Will fix in answer. I don't understand what you're saying about quote_ident, though -- that should be used for the parameter you are passing in; whether that name already exists won't have any effect on the quote_ident function's behavior.