database - 如何将ID字段与其序列匹配？

我有一些pk表是从专用序列中提取的。不幸的是，我假设其中一个序列被两次用于（生成一个id）两个不同的表。是否可以编写一个查询来返回所有字段（字段名总是ID）和用于为这些字段生成默认值的序列？

最佳答案

查询列出与表的id列关联的表名和序列：

select
    nspname as schema,
    relname as table,
    trim(substring(pg_get_expr(adbin, adrelid), e'\'.*\''), '''') as sequence
from pg_attribute a
join pg_attrdef d on adrelid = attrelid and adnum = attnum
join pg_class c on c.oid = attrelid
join pg_namespace n on n.oid = relnamespace
where attname = 'id' and atthasdef
and left(pg_get_expr(adbin, adrelid), 7) = 'nextval';

 schema |    table     |      sequence
--------+--------------+---------------------
 public | test_table   | test_table_id_seq
 public | log          | log_id_seq
 public | master       | master_id_seq
 public | details      | details_id_seq
 ...

您可以跳过条件attname = 'id'。查询将查找以函数nextval()作为默认表达式的列，例如：

select
    nspname as schema,
    relname as table,
    attname as column,
    trim(substring(pg_get_expr(adbin, adrelid), e'\'.*\''), '''') as sequence
from pg_attribute a
join pg_attrdef d on adrelid = attrelid and adnum = attnum
join pg_class c on c.oid = attrelid
join pg_namespace n on n.oid = relnamespace
where atthasdef
and left(pg_get_expr(adbin, adrelid), 7) = 'nextval';

 schema |    table     |  column   |       sequence
--------+--------------+-----------+-----------------------
 public | test_table   | id        | test_table_id_seq
 public | log          | id        | log_id_seq
 public | wallets      | wallet_id | wallets_wallet_id_seq
 public | master       | id        | master_id_seq
 public | details      | id        | details_id_seq
 ...

关于database - 如何将ID字段与其序列匹配？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44268406/