List all sequences in a Postgres db 8.1 with SQL
database, migration, postgresql, sequences, sql
Solution
The following query gives names of all sequences.
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' order BY c.relname;
Typically a sequence is named as `${table}_id_seq`. Simple regex pattern matching will give you the table name.
To get last value of a sequence use the following query:
SELECT last_value FROM test_id_seq;
Problem
I'm converting a db from postgres to mysql. Since i cannot find a tool that does the trick itself, i'm going to convert all postgres sequences to autoincrement ids in mysql with autoincrement value. So, how can i list all sequences in a Postgres DB (8.1 version) with information about the table in which it's used, the next value etc with a SQL query? Be aware that i can't use the `information_schema.sequences` view in the 8.4 release.