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

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

CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING DEFAULTS);

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

CREATE TEMP TABLE t_mytable AS SELECT * FROM mytable;

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

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

Ответы на вопрос(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;
 Francisco Puga07 нояб. 2017 г., 13:53
Спасибо @ErwinBrandstetter. Я включил ваше предложение в ответ.
 Erwin Brandstetter07 нояб. 2017 г., 00:28
Чтобы сделатьserial колонка завершена, последовательность должна бытьнаходящийся в собственности по столбцу:ALTER SEQUENCE t_mytable_id_seq OWNED BY t_mytable.id;
 Henley Chiu05 окт. 2013 г., 21:51
Это лучший ответ, ИМО. Дает вам именно то, что вы хотите!
Решение Вопроса
Postgres 10 или позже

Представлен Postgres 10IDENTITY столбцы соответствует стандарту 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() чтобы получить название последовательности.

dbfiddleВот

Связанные с:

Как сбросить postgres ' последовательность первичного ключа, когда он выходит из синхронизации?Есть ли ярлык для SELECT * FROM?Создание последовательности PostgreSQL для поля (которое не является идентификатором записи)Оригинальный (старый) ответ

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

INSERT INTO new_tbl
SELECT * FROM old_tbl;

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

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

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

Ваш ответ на вопрос