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.
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;
Note, that starting from PostgreSQL 8.4 you can get all information about sequences used in the database via:
SELECT * FROM information_schema.sequences;
Since I'm using a higher version of PostgreSQL (9.1), and was searching for same answer high and low, I added this answer for posterity's sake and for future searchers.
Run: psql -E, and then \ds
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>
sequence info : max value
SELECT * FROM information_schema.sequences;
sequence info : last value
SELECT * FROM <sequence_name>
The relationship between automatically generated sequences ( such as those created for SERIAL columns ) and the parent table is modelled by the sequence owner attribute.
You can modify this relationship using the OWNED BY clause of the ALTER SEQUENCE commmand
e.g. ALTER SEQUENCE foo_id OWNED by foo_schema.foo_table
to set it to be linked to the table foo_table
or ALTER SEQUENCE foo_id OWNED by NONE
to break the connection between the sequence and any table
The information about this relationship is stored in the pg_depend catalogue table.
the joining relationship is the link between pg_depend.objid -> pg_class.oid WHERE relkind = 'S' - which links the sequence to the join record and then pg_depend.refobjid -> pg_class.oid WHERE relkind = 'r' , which links the join record to the owning relation ( table )
This query returns all the sequence -> table dependencies in a database. The where clause filters it to only include auto generated relationships, which restricts it to only display sequences created by SERIAL typed columns.
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' ;
I know this post is pretty old, but I found the solution by CMS to be very useful as I was looking for an automated way to link a sequence to the table AND column, and wanted to share. The use of pg_depend catalog table was the key. I expanded what was done to:
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 column to the list of fields returned. With both the table name and the column name in hand, a call to pg_set_serial_sequence makes it easy to ensure 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 resetting sequences!
I know the question was about postgresql version 8 but I wrote this simple way here for people who want to get sequences in version 10 and upper
you can use the bellow query
select * from pg_sequences
This statement lists the table and column that is associated with each sequence:
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'
more see here link to answer
Kind of a hack, but try this:
select 'select ''' || relname || ''' as sequence, last_value from ' || relname || ' union' FROM pg_catalog.pg_class c WHERE c.relkind IN ('S','');
Remove the last UNION and execute the result
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.
Improvement of 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'
Here is another one which has the schema name beside the sequence name
select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname
Get sequences by each column of each table via parsing of DEFAULT clause. This method provides info about to which column sequences are linked and does not use dependencies which may not exist for some sequences. Even pg_get_serial_sequence(sch.nspname||'.'||tbl.relname, col.attname) function found not all sequences for me!
Solution:
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;
Note that 1 sequence can be used in multiple tables, so it can be listed in multiple rows here.
This function shows the last_value of each sequence.
It outputs a 2 columns table that says the sequence name plus it's 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();
Thanks for your help.
Here is the pl/pgsql function which update each sequence of a database.
---------------------------------------------------------------------------------------------------------
--- 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();
Assuming exec() function declared in this post https://stackoverflow.com/a/46721603/653539 , sequences together with their last values can be fetched using 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
select sequence_name, (xpath('/row/last_value/text()', xml_count))[1]::text::int as last_value
from (
select sequence_schema,
sequence_name,
query_to_xml(format('select last_value from %I.%I', sequence_schema, sequence_name), false, true, '') as xml_count
from information_schema.sequences
where sequence_schema = 'public'
) new_table order by last_value desc;
Here is an example how to use psql to get a list of all sequences with their last_value:
psql -U <username> -d <database> -t -c "SELECT 'SELECT ''' || c.relname || ''' as sequence_name, last_value FROM ' || c.relname || ';' FROM pg_class c WHERE (c.relkind = 'S')" | psql -U <username> -d <database> -t
