用SQL列出Postgres db 8.1中的所有序列

我正在转换数据库从postgres到mysql。

因为我找不到一个工具，做的技巧本身，我要转换所有postgres序列自增id在mysql与自增值。

所以，我怎么能列出所有序列在一个Postgres DB (8.1版本)的信息表中使用，下一个值等与SQL查询?

注意，我不能在8.4版本中使用information_schema.sequences视图。

255249 次浏览
小开

使用-E标志启动psql(“返回由\d和其他反斜杠命令生成的实际查询”)，然后输入\ds命令列出所有序列。你应该会看到这样的东西:

# \ds
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************


List of relations
Schema |              Name              |   Type   | Owner
--------+--------------------------------+----------+-------
public | assignments_id_seq             | sequence | root
public | framework_users_id_seq         | sequence | root
public | lending_items_id_seq           | sequence | root
public | proxy_borrower_requests_id_seq | sequence | root
public | roles_id_seq                   | sequence | root
public | stack_requests_id_seq          | sequence | root
(6 rows)

为了检查一个特定的序列，你可以运行\d <sequence name>:

# \d lending_items_id_seq
********* QUERY **********


(...about four queries...)


**************************


Sequence "public.lending_items_id_seq"
Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.lending_items.id
小开

在经历了一点点痛苦之后，我明白了。

实现这一目标的最佳方法是列出所有表

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

然后，对于每个表，列出具有属性的所有列

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

然后，对于每一列，测试它是否有一个序列

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

然后，得到这个序列的信息

select * from <sequence_name>
小开

部分测试，但看起来基本完成。

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;

该表扬就表扬……它在一定程度上是从一个已知的表上的一个\d记录的SQL反向工程。我相信它也可以更干净，但是嘿，性能不是一个问题。

小开
最佳答案

下面的查询给出了所有序列的名称。

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

通常一个序列被命名为${table}_id_seq。简单的正则表达式模式匹配将为您提供表名。

要获得序列的最后一个值，使用以下查询:

SELECT last_value FROM test_id_seq;
小开

自动生成的序列(例如为SERIAL列创建的序列)和父表之间的关系由序列所有者属性建模。

可以使用ALTER SEQUENCE命令的OWNED BY子句修改此关系

< p >。 ALTER SEQUENCE foo_id属于foo_schema。foo_table < / p >

将其链接到表foo_table

< p >或 ALTER SEQUENCE foo_id属于NONE

中断序列与任何表之间的连接

关于此关系的信息存储在Pg_depend目录表中。

连接关系是pg_depend之间的链接。Objid -> pg_class。oid WHERE relkind = 'S'——将序列链接到连接记录，然后是pg_depend。Refobjid -> pg_class。oid WHERE relkind = 'r'，将连接记录链接到所属关系(表)

此查询返回数据库中所有sequence ->表依赖项。where子句将其过滤为仅包括自动生成的关系，这将其限制为仅显示由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' ;
小开

有点黑，但试试这个:

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

删除最后一个UNION并执行结果

小开

改进了之前的答案:

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

注意，从PostgreSQL 8.4开始，你可以通过以下方式获得关于数据库中使用的序列的所有信息:

SELECT * FROM information_schema.sequences;

由于我使用的是更高版本的PostgreSQL(9.1)，并且正在搜索相同的答案，所以为了子孙后代和未来的搜索者，我添加了这个答案。

小开

我知道这篇文章很老了，但我发现CMS的解决方案非常有用，因为我正在寻找一种自动的方法来链接一个序列到表和列，并想分享。使用pg_depend目录表是关键。我扩展了所做的:

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' ;

这个版本将column添加到返回的字段列表中。有了表名和列名，调用pg_set_serial_sequence可以很容易地确保数据库中的所有序列都被正确设置。例如:

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$;

希望这有助于重置序列!

小开

该语句列出了与每个序列相关联的表和列:

代码:

    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'

更多见这里链接到答案

小开

谢谢你的帮助。

下面是pl/pgsql函数，它更新数据库的每个序列。

---------------------------------------------------------------------------------------------------------
--- 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();
小开

序列信息:最大值

SELECT * FROM information_schema.sequences; < /代码> < / p >

序列信息:最后一个值

SELECT * FROM < < /代码> < / p >

小开

下面是另一个序列名旁边的模式名

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

通过解析DEFAULT子句，按每个表的每一列获取序列。该方法提供了有关链接到哪些列序列的信息，而不使用依赖关系，因为某些序列可能不存在。甚至pg_get_serial_sequence(sch.nspname||'.'||tbl.relname, col.attname)函数也为我找到了并不是所有的序列!

解决方案:

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;

注意，一个序列可以在多个表中使用，因此可以在这里的多行中列出它。

小开

这个函数显示每个序列的last_value。

它输出一个2列的表，上面写着序列名加上它最后生成的值。

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();
小开

假设在本文https://stackoverflow.com/a/46721603/653539中声明了exec()函数，序列及其最后一个值可以使用单个查询获取:

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;
小开

下面是一个示例，如何使用psql获取包含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

小开

我知道这个问题是关于postgresql版本8的，但我在这里写了这个简单的方法，供那些想在版本10或更高版本中获得序列的人使用

您可以使用下面的查询

select * from pg_sequences

view-pg-sequences

小开

获取所有序列:

select * from pg_sequences;

PSQL:

\ds
\ds+
\ds *actor*

\ds *actor*将获取序列名中包含演员字符的所有序列。


提交答案