Полностью копирование таблицы postgres с помощью SQL

Ответ 1

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

select * into newtable from oldtable

Это создаст новую таблицу со старыми таблицами, но не индексами. Затем вам нужно создать индексы и последовательности и т.д. Самостоятельно. Вы можете получить список всех индексов в таблице с помощью команды:

select indexdef from pg_indexes where tablename='oldtable';

Затем запустите psql -E для доступа к вашему db и используйте \d, чтобы посмотреть на старую таблицу. Затем вы можете управлять этими двумя запросами, чтобы получить информацию о последовательностях:

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(oldtable)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (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),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Замените это 74359 выше на ту, которую вы получили из предыдущего запроса.

Ответ 2

Функция create table as в PostgreSQL теперь может быть ответом, который ищет OP.

https://www.postgresql.org/docs/9.5/static/sql-createtableas.html

create table my_table_copy as
  select * from my_table

Это создаст идентичную таблицу с данными.

Добавление with no data скопирует схему без данных.

create table my_table_copy as
  select * from my_table
with no data

Это создаст таблицу со всеми данными, но без индексов и триггеров и т.д.

create table my_table_copy (like my_table including all)

Синтаксис create table, такой как синтаксис, будет включать все триггеры, индексы, ограничения и т.д. Но не включать данные.

Ответ 3

Ближайшая "команда чуда" - это что-то вроде

pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -

В частности, это заботится о создании индексов после загрузки данных таблицы.

Но это не reset последовательности; вам придется script, что вы сами.

Ответ 4

Внимание:

Все ответы, которые используют pg_dump и любое регулярное выражение для замены имени исходной таблицы, действительно опасны. Что делать, если ваши данные содержат подстроку, которую вы пытаетесь заменить? Вы в конечном итоге измените свои данные!

Я предлагаю двухпроходное решение:

• исключить строки данных из дампа, используя некоторые специфичные для данных regexp
• выполнить поиск и замену на оставшихся строках

Вот пример, написанный на Ruby:

ruby -pe 'gsub(/(members?)/, "\\1_copy_20130320") unless $_ =~ /^\d+\t.*(?:t|f)$/' < members-production-20130320.sql > copy_members_table-20130320.sql

В приведенном выше примере я пытаюсь скопировать таблицу "members" в "members_copy_20130320". Моим конкретным данным regexp является /^\d +\t. * (?: t | f) $/

Вышеупомянутый тип решения работает для меня. Предостережение emptor...

изменить

ОК, здесь другой путь в синтаксисе псевдо-оболочки для людей, не имеющих регулярных выражений:

• pg_dump -s -t mytable mydb > mytable_schema.sql
• имя таблицы поиска и замены в mytable_schema.sql > mytable_copy_schema.sql

• psql -f mytable_copy_schema.sql mydb

• pg_dump -a -t mytable mydb > mytable_data.sql

• заменить "mytable" в нескольких операциях SQL, предшествующих разделу данных
• psql -f mytable_data.sql mydb

Ответ 5

Очевидно, вы хотите "перестроить" таблицу. Если вы хотите только перестроить таблицу, а не копировать ее, то вместо этого вы должны использовать CLUSTER.

SELECT count(*) FROM table; -- make a seq scan to make sure the table is at least
                            -- decently cached
CLUSTER someindex ON table;

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

Если ваша таблица слишком велика для кэширования, CLUSTER может быть медленным, хотя.

Ответ 6

Чтобы полностью скопировать таблицу, включая структуру таблицы и данные, используйте следующую инструкцию:

CREATE TABLE new_table AS 
TABLE existing_table;

Чтобы скопировать структуру таблицы без данных, добавьте предложение WITH NO DATA в оператор CREATE TABLE следующим образом:

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

Чтобы скопировать таблицу с частичными данными из существующей таблицы, используйте следующую инструкцию:

CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;

Ответ 7

создать таблицу newTableName (например, oldTableName, включая индексы); insert в newTableName select * from oldTableName

Это сработало для меня 9.3