Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

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.

1 Answer

0 votes
by (40.7k points)

The below query will give you the names of all sequences.

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

Typically the sequence is named as ${table}_id_seq. And the simple regex pattern matching will give you the table name.To get the last value of a sequence use the query as follows:

SELECT last_value FROM test_id_seq;

Related questions

0 votes
1 answer
asked Jul 4, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 12, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer

Browse Categories