Как скопировать структуру и содержимое таблицы, но с отдельной последовательностью?

Я пытаюсь настроить временные таблицы для целей юнит-тестирования. До сих пор мне удалось создать временную таблицу, которая копирует структуру существующей таблицы:

CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING DEFAULTS);

Но для этого не хватает данных из исходной таблицы. Я могу скопировать данные во временную таблицу, используя вместо этого оператор CREATE TABLE AS:

CREATE TEMP TABLE t_mytable AS SELECT * FROM mytable;

Но тогда структура t_mytable не будет идентичной, например, размеры столбцов и значения по умолчанию будут разными. Есть ли одно утверждение, которое копирует все?

Другая проблема с первым запросом, использующим LIKE заключается в том, что ключевой столбец все еще ссылается на SEQUENCE исходной таблицы и, таким образом, увеличивает его при вставке. Есть ли простой способ создать новую таблицу с собственной последовательностью, или мне нужно будет установить новую последовательность вручную?

Ответы

Ответ 1

Postgres 10 или позже

Postgres 10 представил столбцы IDENTITY соответствующие стандарту SQL (с небольшими расширениями). Столбец ID вашей таблицы будет выглядеть примерно так:

id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY

Синтаксис в руководстве.
Использование этого вместо традиционного serial столбца позволяет избежать проблем с последовательностями. Столбцы IDENTITY используют эксклюзивные, выделенные последовательности автоматически, даже когда спецификация копируется с помощью LIKE. Руководство:

Любые спецификации идентификаторов скопированных определений столбцов будут скопированы только в том случае, если указано INCLUDING IDENTITY. Новая последовательность создается для каждого столбца идентификации новой таблицы отдельно от последовательностей, связанных со старой таблицей.

А также:

INCLUDING ALL - это сокращенная форма INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.

Решение стало проще:

CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING ALL);
INSERT INTO t_mytable TABLE mytable;
SELECT setval(pg_get_serial_sequence('t_mytable', 'id'), max(id)) FROM tbl;

Как показано, вы все еще можете использовать setval() для установки текущего значения последовательности. Один SELECT делает свое дело. Используйте pg_get_serial_sequence() чтобы получить имя последовательности.

дБ <> скрипка здесь

Связанные с:

Оригинальный (старый) ответ

Вы можете взять сценарий создания из дампа базы данных или графический пользовательский интерфейс, такой как pgAdmin (который выполняет обратное проектирование сценариев создания объектов базы данных), создать идентичную копию (с отдельной последовательностью для serial столбца), а затем выполнить:

INSERT INTO new_tbl
SELECT * FROM old_tbl;

Копия не может быть идентичной на 100%, если обе таблицы находятся в одной и той же схеме. Очевидно, что имя таблицы должно быть другим. Имена индексов тоже будут конфликтовать. Получение серийных номеров из одной и той же последовательности, вероятно, также не в ваших интересах. Таким образом, вы должны (по крайней мере) настроить имена.

Помещение копии в другую схему позволяет избежать всех этих конфликтов. При создании временной таблицы из обычной таблицы, как вы продемонстрировали, это автоматически происходит, поскольку временные таблицы находятся в собственной временной схеме.

Или посмотрите ответ Франциско на код DDL, который нужно скопировать напрямую.

Ответ 2

Я использую следующий код для этого:

CREATE TABLE t_mytable (LIKE mytable INCLUDING ALL);
ALTER TABLE t_mytable ALTER id DROP DEFAULT;
CREATE SEQUENCE t_mytable_id_seq;
INSERT INTO t_mytable SELECT * FROM mytable;
SELECT setval('t_mytable_id_seq', (SELECT max(id) FROM t_mytable), true);
ALTER TABLE t_mytable ALTER id SET DEFAULT nextval('t_my_table_id_seq');
ALTER SEQUENCE t_mytable_id_seq OWNED BY t_mytable.id;