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

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

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 исходной таблицы и, таким образом, увеличивает его при вставке. Есть ли простой способ создать новую таблицу со своей собственной последовательностью, или мне придется настроить новую последовательность вручную?


postgresql sql create-table temp-tables
person Stefan Majewsky    schedule 04.09.2012    source источник

Ответы (2)


arrow_upward
8
arrow_downward

Постгрес 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()]6 получает имя последовательности.

db‹›fiddle здесь

Связанный:

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

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

INSERT INTO new_tbl
SELECT * FROM old_tbl;

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

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

Или посмотрите на ответ Франсиско, чтобы код DDL копировался напрямую.

person Erwin Brandstetter    schedule 04.09.2012

arrow_upward
27
arrow_downward

Я использую следующий код, чтобы сделать это:

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;
person Francisco Puga    schedule 04.09.2013
comment
Это лучший ответ, имхо. Дает вам ИМЕННО то, что вы хотите! - person Henley; 05.10.2013
comment
Чтобы сделать столбец serial полным, последовательность должна принадлежать столбцу: ALTER SEQUENCE t_mytable_id_seq OWNED BY t_mytable.id; - person Erwin Brandstetter; 07.11.2017
comment
Спасибо @ErwinBrandstetter. Я включаю вашу фразу в ответ. - person Francisco Puga; 07.11.2017