How to delete unused sequences?

Question

We are using PostgreSQL. My requirement is to delete unused sequences from my database. For example, if I create any table through my application, one sequence will be created, but for deleting the table we are not deleting the sequence, too. If want to create the same table another sequence is being created.

Example: table: file; automatically created sequence for id coumn: file_id_seq

When I delete the table file and create it with same name again, a new sequence is being created (i.e. file_id_seq1). I have accumulated a huge number of unused sequences in my application database this way.

How to delete these unused sequences?

Answer 1

First off, a sequence that is created automatically for a serial column is deleted automatically, when the column (or table it is in) is deleted. The problem you describe should not exist to begin with. Only very old versions of PostgreSQL did not do that. 7.4 or older?

Solution for the problem:

This query will generate the DDL commands to delete all "unbound" sequences in the database it is executed in:

SELECT string_agg('DROP SEQUENCE ' || c.oid::regclass, '; ') || ';' AS ddl
FROM   pg_class       c
LEFT   JOIN pg_depend d ON d.refobjid = c.oid
                       AND d.deptype <> 'i'
WHERE  c.relkind = 'S'
AND    d.refobjid IS NULL;

The cast to regclass in c.oid::regclass automatically schema-qualifies sequence names where necessary according to the current search_path. See:

• How to check if a table exists in a given schema
• How does the search_path influence identifier resolution and the "current schema"

Result:

DROP SEQUENCE foo_id_seq;
DROP SEQUENCE bar_id_seq;
...

Execute the result to drop all sequences that are not bound to a serial column (or any other column). Study the meaning of columns and tables here.

Careful though! It does not mean those sequences aren't in use otherwise. There are a number of use cases where sequences are created as standalone objects. For instance if you want multiple columns to share one sequence. You should know exactly what you are doing.

However, you cannot delete sequences bound to a serial column this way. So the operation is safe in this respect.

DROP SEQUENCE test_id_seq

Result:

ERROR:  cannot drop sequence test_id_seq because other objects depend on it
DETAIL:  default for table test column id depends on sequence test_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.