Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

database - List all sequences in a Postgres db 8.1 with SQL

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The following query gives names of all sequences.

SELECT c.relname 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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...