How to delete unused sequences?
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.
user1023877
Updated on June 19, 2022Comments
-
user1023877 almost 2 years
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 forid
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?
-
user1023877 over 12 yearsBut it is too difficult to drop all sequences one by one manually
-
DrColossos over 12 years@user1023877 you should than write a script that does something similar to this. You could read the syscatalog to see dependencies, but writing something like this can be tedious as well. Making it short: I don't think there is an easy way for such a task.
-
user1023877 over 12 yearsOkay Thank you. could you refer any links which will provide information about how to write script?
-
DrColossos over 12 yearsTry the official docs and infos for the system catalog
-
user1023877 over 12 yearsThank you. I will go through it.
-
user1023877 over 12 yearsWhat I do is same like your solution. It is correct way or not i dont know. i get all the sequences. then saved in file.sql then i run the file. code is:
-
user1023877 over 12 years
\o d:/test1.sql SELECT 'drop sequence ' || c.relname || ';' FROM pg_class c WHERE (c.relkind = 'S'); \o \i d:/test1.sql
-
Erwin Brandstetter over 12 years@user1023877: That tries to drop any and all sequences. Which should fail if any "bound" sequence exists.
-
user1023877 over 12 yearsso This is not the proper solution right, so i will move on to another solution .thank you.
-
user1023877 over 12 yearsyes, i will move on to the another solution means your solution only. thank you.
-
Alkanshel over 8 yearsI'm running postgres 9.3.4 and a table's sequence is NOT deleted for me when the table is dropped. I still have to delete the sequence manually.
-
Erwin Brandstetter over 8 years@Amalgovinus: Then the sequence is not owned by the PK column like it would be when created with the
serial
pseudo-type. See: stackoverflow.com/a/24659884/939860 or stackoverflow.com/a/10002134/939860 and -
Alkanshel over 8 yearsYou're right, my problem was that I was lacking a PK on the sequence field.
-
Erwin Brandstetter over 8 years@Amalgovinus: I am sure I am right, but the PK has nothing to with it. :) The ownership is the important piece here.
-
Felipe Andrade over 7 yearsJust reporting that this problem can happen in Postgres 8.4, but not happened every time for me. Maybe it is a bug, maybe somebody messed up something in my database, I don't know.
-
Erwin Brandstetter about 7 years@SteveGlick: Thanks for your edit. You were right about adding the schema. I just replaced it with more elegant code.