List all sequences in a Postgres db 8.1 with SQL

Question

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.

Answer 1

Run: psql -E, and then \ds

Answer 2

after a little bit of pain, i got it.

the best way to achieve this is to list all tables

select * from pg_tables where schemaname = '<schema_name>'

and then, for each table, list all columns with attributes

select * from information_schema.columns where table_name = '<table_name>'

then, for each column, test if it has a sequence

select pg_get_serial_sequence('<table_name>', '<column_name>')

and then, get the information about this sequence

select * from <sequence_name>

Answer 3

Partially tested but looks mostly complete.

select *
  from (select n.nspname,c.relname,
               (select substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                  from pg_catalog.pg_attrdef d
                 where d.adrelid=a.attrelid
                   and d.adnum=a.attnum
                   and a.atthasdef) as def
          from pg_class c, pg_attribute a, pg_namespace n
         where c.relkind='r'
           and c.oid=a.attrelid
           and n.oid=c.relnamespace
           and a.atthasdef
           and a.atttypid=20) x
 where x.def ~ '^nextval'
 order by nspname,relname;

Credit where credit is due... it's partly reverse engineered from the SQL logged from a \d on a known table that had a sequence. I'm sure it could be cleaner too, but hey, performance wasn't a concern.

Answer 4

The following query gives names of all sequences.

SELECT c.relname AS FROM pg_class c WHERE c.relkind = 'S';

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;