List all sequences in a Postgres db 8.1 with SQL
To obtain all sequences' names in your PostgreSQL 8.1 database, utilize the following SQL query:
This handy SQL snippet sifts sequences ('S') from the pg_class system catalog, excluding sequences tied to system schemas pg_catalog and information_schema. You'll end up with a neat list of sequences from user-defined schemas in your database.
Extracting table names and sequence values in PostgreSQL 8.1
Extracting table names from sequence names
In PostgreSQL, sequence names often mold to the convention ${table}_id_seq. Unfortunately, PostgreSQL 8.1 doesn't support regex substring functions, so you'll have to bank on a workaround: bash your sequence names into the desired format using your application's language of choice. For Pythonistas, substring will be your go-to function here.
Quick tip on sequence values
Getting the last value of a sequence? Easy as pie!
Replace sequence_name with the sequence name you're curious about and boom! — you've got your last value without incrementing anything.
Under-the-bonnet peek at sequences with psql
In the hands-on world of databases, sometimes the command-line can be your trusty companion. Enter psql with the -E flag, which will spill the beans on what happens when you throw psql's commands at it:
For an up-close look at a particular sequence:
Time to upgrade?
We can't end the conversation without addressing the Triceratops in the room: PostgreSQL 8.1. With all due respect, it's a little long in the tooth. Upgrade to a hotter, newer number (think 8.4+) for improved functionality like information_schema.sequences. Not to mention, you're also getting improved security. Trust us — your DB will love you for it.
Keeping track of sequences: the smart way
Detecting column-sequence associations
Use pg_get_serial_sequence to verify which columns are dancing with which sequences:
Knowing your sequences' dance partners can keep your columns from stepping on any toes.
Crossing the great migration plains
If your sights are set on migrating to MySQL or similar, remember to pack your custom scripts to morph your Postgres sequences into MySQL's autoincrement IDs. Also, you'd want a backup — nobody wants a toppled pile of lost data.
New doesn't always mean better...
...but it does when it means version compatibility. In more recent PostgreSQL versions (8.4 and onwards), you have information_schema.sequences to get sequence info. But as an 8.1 user, you get the vintage experience —recalling arts like the pg_class and pg_namespace catalogs for sequence details.
A tale of two DBMS
The nitty-gritty of DB-to-DB migration isn't for the faint of heart. Beware of how Postgres sequences translate to MySQL's auto_increment, or your data integrity can stake a serious hit.
Was this article helpful?