Список всех НЕСОЗДАННЫХ последовательностей для схемы в PostgreSQL

В PostgreSQL есть 2 типа последовательностей:

  • Последовательности, созданные пользователем с помощью CREATE SEQUENCE
  • Последовательности, сгенерированные БД для поддержки столбца типа SERIAL

INFORMATION_SCHEMA.SEQUENCES возвращает оба типа последовательностей.

Какой оператор SQL может получить список последовательностей, созданных пользователем (БЕЗ сгенерированных БД) для конкретной схемы?

P.S.: я использую PostgreSQL 9


postgresql sql sequences
person Axel Fontaine    schedule 26.03.2011    source источник

Ответы (2)


arrow_upward
5
arrow_downward

Для сгенерированной последовательности столбец "владелец" будет определен автоматически, так что может быть отличительным фактором.

Но это можно сделать и вручную, поэтому невозможно определить разницу между:

create table foo (
   id_col serial not null
);

а также

create table foo (
   id_col integer not null
);
create sequence foo_id_col_seq owned by foo.id_col;


Но если это вас устраивает, следующее утверждение может дать вам эту информацию:

SELECT s.relname as sequence_name,  
       t.relname as related_table, 
       a.attname as related_column,
       d.deptype
FROM pg_class s 
   JOIN pg_depend d ON d.objid = s.oid 
   LEFT JOIN pg_class t ON d.refobjid = t.oid 
   LEFT JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) 
WHERE s.relkind = 'S';

Вы получите по крайней мере одну строку для каждой последовательности по этому запросу: зависимость от схемы, в которой она была создана.

Для последовательности, принадлежащей столбцу, вы получите еще одну строку с таблицей и столбцом, которым она принадлежит.

person a_horse_with_no_name    schedule 26.03.2011
comment
pg_catalog.attr_def содержит исходное определение столбца. \d отобразит только первый foo с модификаторами nextval, второй будет иметь только not null. - person nate c; 28.03.2011

arrow_upward
0
arrow_downward

Единственная разница может (как минимум) заключаться в том, где она определена. Ввод \d yourtable отобразит серийные номера в столбце модификаторов как nextval(...), но последовательности, определенные позже, не будут. pg_catalog.attr_def содержит данные для фактических строк, которые использовались для определения.

Я согласен с лошадью, что функционально разницы нет. Это похоже на объявление чего-то в одной или двух строках, но происходит то же самое. БД генерирует все эти последовательности, и нет пользовательских последовательностей. Единственная разница заключается в том, как они объявляются, и единственный способ сохранения этой информации — фактические строки, определяющие столбец, хранятся в системных каталогах.

Из системных таблиц:

create view check_seq as
SELECT a.attname,
    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 "column def"

FROM pg_catalog.pg_attribute a
join pg_catalog.pg_class c on a.attrelid = c.oid
WHERE a.attnum > 0 AND NOT a.attisdropped
and c.relname in ('foo')
ORDER BY a.attnum
;

Примеры:

create table foo (
   id_col serial not null
);
select * from check_seq;

 attname | relname |             column def           
 ---------+---------+-------------------------------------
  id_col  | foo     | nextval('foo_id_col_seq'::regclass)


drop table foo;
create table foo (
   id_col integer not null
);
create sequence foo_id_col_seq owned by foo.id_col;
select * from check_seq;

 attname | relname | column def
 ---------+---------+------------
  id_col  | foo     |
person nate c    schedule 28.03.2011