Скопируйте таблицу (включая индексы) в postgres

Создайте новую таблицу с помощью select, чтобы получить нужные данные. Затем замените старую таблицу новой.

create table mynewone as select * from myoldone where ...
mess (re-create) with indexes after the table swap.

[CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace ]
    AS query][1]  

Вот пример

CREATE TABLE films_recent AS
  SELECT * FROM films WHERE date_prod >= '2002-01-01';

Другой способ создать новую таблицу из первой - использовать

    CREATE TABLE films_recent (LIKE films INCLUDING INDEXES);  

    INSERT INTO films_recent
         SELECT *
           FROM books
          WHERE date_prod >= '2002-01-01';  

Обратите внимание, что Postgresql имеет пластырь для исправления проблем с табличным пространством, если используется второй метод.

I have a postgres table. I need to delete some data from it.

Я полагаю, что ...

delete from yourtable
where <condition(s)>

... по какой-то причине не работает. (Не хотите поделиться этой причиной?)

I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need.

Загляните в pg_dump и pg_restore. Использование pg_dump с некоторыми умными опциями и, возможно, редактирование вывода перед pg_restoring может помочь.

Поскольку вы проводите анализ данных типа «что, если», мне интересно, не лучше ли вам использовать представления.

Вы можете определить представление для каждого сценария, который хотите протестировать, на основе отрицания того, что вы хотите исключить. То есть определите представление, основанное на том, что вы хотите включить. Например, если вам нужно «окно» для данных, в котором вы «удалили» строки, где X = Y, вы должны создать представление в виде строк, где (X! = Y).

Представления хранятся в базе данных (в системном каталоге) как их определяющий запрос. Каждый раз, когда вы запрашиваете представление, сервер базы данных ищет базовый запрос, который его определяет, и выполняет его (AND с любыми другими условиями, которые вы использовали). У этого подхода есть несколько преимуществ:

1. Вы никогда не дублируете какую-либо часть своих данных.
2. Индексы, уже используемые для базовой таблицы (исходная, «настоящая» таблица), будут использоваться (как считает оптимизатор запросов) при запросе каждого представления / сценария. Нет необходимости переопределять или копировать их.
3. Поскольку представление является «окном» (НЕ рисунком) «реальных» данных в базовой таблице, вы можете добавлять / обновлять / удалять в своей базовой таблице и просто повторно запрашивать сценарии представления без необходимости воссоздавать что-либо как данные меняются со временем.

Конечно, есть компромисс. Поскольку представление - это виртуальная таблица, а не «настоящая» (базовая) таблица, вы фактически выполняете (возможно, сложный) запрос каждый раз, когда обращаетесь к нему. Это может немного замедлить работу. Но может и не быть. Это зависит от многих факторов (размер и характер данных, качество статистики в системном каталоге, скорость оборудования, загрузка и многое другое). Вы не узнаете, пока не попробуете. Если (и только если) вы действительно обнаружите, что производительность неприемлемо низкая, вы можете рассмотреть другие варианты. (Материализованные представления, копии таблиц, ... все, что меняет пространство на время.)

Новый PostgreSQL (начиная с версии 8.3 согласно документации) может использовать "ВКЛЮЧАЮЩИЕ ИНДЕКСЫ":

# select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
(1 row)

Как видите, я тестирую 8.3.

Теперь создадим таблицу:

# create table x1 (id serial primary key, x text unique);
NOTICE:  CREATE TABLE will create implicit sequence "x1_id_seq" for serial column "x1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x1_pkey" for table "x1"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x1_x_key" for table "x1"
CREATE TABLE

И посмотрите, как это выглядит:

# \d x1
                         Table "public.x1"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x1_pkey" PRIMARY KEY, btree (id)
    "x1_x_key" UNIQUE, btree (x)

Теперь мы можем скопировать структуру:

# create table x2 ( like x1 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x2_pkey" for table "x2"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x2_x_key" for table "x2"
CREATE TABLE

И проверим структуру:

# \d x2
                         Table "public.x2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x2_pkey" PRIMARY KEY, btree (id)
    "x2_x_key" UNIQUE, btree (x)

Если вы используете PostgreSQL до 8.3, вы можете просто использовать pg_dump с опцией «-t», чтобы указать 1 таблицу, изменить имя таблицы в дампе и загрузить ее снова:

=> pg_dump -t x2 | sed 's/x2/x3/g' | psql
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

А теперь таблица:

# \d x3
                         Table "public.x3"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x3_pkey" PRIMARY KEY, btree (id)
    "x3_x_key" UNIQUE, btree (x)

Простой способ - включить все:

CREATE TABLE new_table (LIKE original_table INCLUDING ALL);

В сети много ответов, один из них можно найти здесь.

В итоге я сделал что-то вроде этого:

create table NEW ( like ORIGINAL including all);
insert into NEW select * from ORIGINAL

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