List all sequences in Postgres db 8.1 with SQL

Question

I am converting db from postgres to mysql.

Since I cannot find a tool that does the trick myself, I am going to convert all postgres sequences to autoincrement ids in mysql using the auto increment value.

So, how can I list all the sequences in the Postgres database ( 8.1 ) with information about the table in which it was used, the next value, etc. with SQL query?

Remember that I cannot use the information_schema.sequences view in release 8.4.

Answer 1

The following query contains the names of all sequences.

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

Usually the sequence is called ${table}_id_seq . Simple pattern matching will give you a table name.

To get the last value of a sequence, use the following query:

 SELECT last_value FROM test_id_seq; 

Answer 2

Run: psql -E and then \ds

Answer 3

Please note that starting with PostgreSQL 8.4 you can get all the sequence information used in the database with:

 SELECT * FROM information_schema.sequences; 

---

Since I use a higher version of PostgreSQL (9.1) and searched for the same high and low answer, I added this answer for posterity and future search engines.

Answer 4

after a bit of pain, I understood.

the best way to achieve this is to list all the tables

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

and then for each table list all the columns with attributes

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

then for each column check if it has a sequence

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

and then get information about this sequence

 select * from <sequence_name> 

Answer 5

The relationship between automatically generated sequences (such as those created for SERIAL columns) and the parent table is modeled by the sequence owner attribute.

You can change this ratio using the OWNED BY ALTER SEQUENCE commmand clause

eg. ALTER SEQUENCE foo_id OWNED by foo_schema.foo_table

to bind it to foo_table

or ALTER SEQUENCE foo_id ASSEMBLED NONE

to break the join between a sequence and any table

Information about this link is stored in the pg_depend directory table .

the join relation is the relationship between pg_depend.objid → pg_class.oid WHERE relkind = 'S' - which associates the sequence with the union record, and then pg_depend.refobjid → pg_class.oid WHERE relkind = 'r', which associates the connection record with the owner relation (table)

This query returns all the dependencies of the sequence -> tables in the database. The where clause filters it only to include automatically generated relationships, which limits it to displaying only sequences created by columns printed by SERIAL.

 WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname , c.relkind, c.relname AS relation FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ), sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'), tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' ) SELECT s.fqname AS sequence, '->' as depends, t.fqname AS table FROM pg_depend d JOIN sequences s ON s.oid = d.objid JOIN tables t ON t.oid = d.refobjid WHERE d.deptype = 'a' ; 

Answer 6

sequence information: maximum value

SELECT * FROM information_schema.sequences;

sequence information: last value

SELECT * FROM <sequence_name>

Answer 7

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; 

A loan in which a loan must be ... partially deferred from SQL registered from a \ d in a known table having a sequence. I'm sure it might be cleaner, but hey, performance wasn't a problem.

Answer 8

I know this post is pretty old, but I found the CMS solution very useful as I was looking for an automatic way to chain the sequence into an AND column and want to share it. Using the pg_depend directory key table. I expanded what was done for:

 WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname , c.relkind, c.relname AS relation FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ), sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'), tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' ) SELECT s.fqname AS sequence, '->' as depends, t.fqname AS table, a.attname AS column FROM pg_depend d JOIN sequences s ON s.oid = d.objid JOIN tables t ON t.oid = d.refobjid JOIN pg_attribute a ON a.attrelid = d.refobjid and a.attnum = d.refobjsubid WHERE d.deptype = 'a' ; 

This version adds a column to the list of returned fields. Both the table name and the column name in the hand, calling pg_set_serial_sequence makes it easy to verify that all sequences in the database are set correctly, For example:

 CREATE OR REPLACE FUNCTION public.reset_sequence(tablename text, columnname text) RETURNS void LANGUAGE plpgsql AS $function$ DECLARE _sql VARCHAR := ''; BEGIN _sql := $$SELECT setval( pg_get_serial_sequence('$$ || tablename || $$', '$$ || columnname || $$'), (SELECT COALESCE(MAX($$ || columnname || $$),1) FROM $$ || tablename || $$), true)$$; EXECUTE _sql; END; $function$; 

Hope this helps someone with re-settings!

Answer 9

Kind of hack, but try the following:

select 'select' '' || relname || '' 'as a sequence, last_value from' || relname || "union" FROM pg_catalog.pg_class c WHERE c.relkind IN ('S', '');

Delete the last UNION and execute the result

Answer 10

Improving the previous answer:

 select string_agg('select sequence_name, last_value from ' || relname, chr(13) || 'union' || chr(13) order by relname) from pg_class where relkind ='S' 

Answer 11

This statement lists the table and column associated with each sequence:

the code:

  SELECT t.relname as related_table, a.attname as related_column, s.relname as sequence_name FROM pg_class s JOIN pg_depend d ON d.objid = s.oid JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) JOIN pg_namespace n ON n.oid = s.relnamespace WHERE s.relkind = 'S' AND n.nspname = 'public' 

For more details see the link for the answer here.

Answer 12

Thanks for your help.

Here is the pl / pgsql function, which updates each database sequence.

 --------------------------------------------------------------------------------------------------------- --- Nom : reset_sequence --- Description : Générique - met à jour les séquences au max de l'identifiant --------------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION reset_sequence() RETURNS void AS $BODY$ DECLARE _sql VARCHAR := ''; DECLARE result threecol%rowtype; BEGIN FOR result IN WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname ,c.relkind, c.relname AS relation FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ), sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'), tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' ) SELECT s.fqname AS sequence, t.fqname AS table, a.attname AS column FROM pg_depend d JOIN sequences s ON s.oid = d.objid JOIN tables t ON t.oid = d.refobjid JOIN pg_attribute a ON a.attrelid = d.refobjid and a.attnum = d.refobjsubid WHERE d.deptype = 'a' LOOP EXECUTE 'SELECT setval('''||result.col1||''', COALESCE((SELECT MAX('||result.col3||')+1 FROM '||result.col2||'), 1), false);'; END LOOP; END;$BODY$ LANGUAGE plpgsql; SELECT * FROM reset_sequence(); 

Answer 13

Here is another one that has a schema name next to a sequence name

 select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname 

Answer 14

Get sequences for each column of each table by parsing the DEFAULT clause. This method provides information about which column sequences are related and does not use dependencies that may not exist for some sequences. Even pg_get_serial_sequence(sch.nspname||'.'||tbl.relname, col.attname) did not find all the sequences for me!

Decision:

 SELECT seq_sch.nspname AS sequence_schema , seq.relname AS sequence_name , seq_use."schema" AS used_in_schema , seq_use."table" AS used_in_table , seq_use."column" AS used_in_column FROM pg_class seq INNER JOIN pg_namespace seq_sch ON seq_sch.oid = seq.relnamespace LEFT JOIN ( SELECT sch.nspname AS "schema" , tbl.relname AS "table" , col.attname AS "column" , regexp_split_to_array( TRIM(LEADING 'nextval(''' FROM TRIM(TRAILING '''::regclass)' FROM pg_get_expr(def.adbin, tbl.oid, TRUE) ) ) , '\.' ) AS column_sequence FROM pg_class tbl --the table INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace --schema INNER JOIN pg_attribute col ON col.attrelid = tbl.oid --columns INNER JOIN pg_attrdef def ON (def.adrelid = tbl.oid AND def.adnum = col.attnum) --default values for columns WHERE tbl.relkind = 'r' --regular relations (tables) only AND col.attnum > 0 --regular columns only AND def.adsrc LIKE 'nextval(%)' --sequences only ) seq_use ON (seq_use.column_sequence [1] = seq_sch.nspname AND seq_use.column_sequence [2] = seq.relname) WHERE seq.relkind = 'S' --sequences only ORDER BY sequence_schema, sequence_name; 

Please note that 1 sequence can be used in several tables, so it can be indicated in several lines here.

Answer 15

This function shows the last value of each sequence.

It displays a two-column table listing the name of the sequence plus its last generated value.

 drop function if exists public.show_sequence_stats(); CREATE OR REPLACE FUNCTION public.show_sequence_stats() RETURNS TABLE(tablename text, last_value bigint) LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000 AS $BODY$ declare r refcursor; rec record; dynamic_query varchar; BEGIN dynamic_query='select tablename,last_value from ('; open r for execute 'select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = ''S'' order by nspname'; fetch next from r into rec; while found loop dynamic_query=dynamic_query || 'select '''|| rec.nspname || '.' || rec.relname ||''' "tablename",last_value from ' || rec.nspname || '.' || rec.relname || ' union all '; fetch next from r into rec; end loop; close r; dynamic_query=rtrim(dynamic_query,'union all') || ') x order by last_value desc;'; return query execute dynamic_query; END; $BODY$; select * from show_sequence_stats(); 

Answer 16

Assuming that the exec() function declared in this post is https://stackoverflow.com/a/212960/2129 , the sequences with their latest values ​​can be selected using a single query:

 select s.sequence_schema, s.sequence_name, (select * from exec('select last_value from ' || s.sequence_schema || '.' || s.sequence_name) as e(lv bigint)) last_value from information_schema.sequences s