Postgres에서 테이블 (인덱스 포함) 복사

Postgres에서 테이블 (인덱스 포함) 복사

---

postgres 테이블이 있습니다. 일부 데이터를 삭제해야합니다. 임시 테이블을 만들고 데이터를 복사하고 인덱스를 다시 만들고 필요한 행을 삭제하려고했습니다. 이 원본 테이블이 데이터 소스이기 때문에 원본 테이블에서 데이터를 삭제할 수 없습니다. 어떤 경우에는 X 삭제에 의존하는 결과를 얻어야하고, 다른 경우에는 Y를 삭제해야합니다. 따라서 항상 주변에 있고 사용할 수 있도록 모든 원본 데이터가 필요합니다.

그러나 테이블을 다시 만들고 다시 복사하여 인덱스를 다시 만드는 것은 약간 어리석은 것처럼 보입니다. 어쨌든 postgres에 "구조, 데이터 및 인덱스를 포함하여이 테이블의 완전한 개별 복사본을 원합니다"라고 말하는 것이 있습니까?

불행히도 PostgreSQL에는 "CREATE TABLE .. LIKE X INCLUDING INDEXES '가 없습니다.

---

새 PostgreSQL (docs에 따르면 8.3 이후)은 "INCLUDING INDEXES"를 사용할 수 있습니다.

# 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 이전 버전을 사용하는 경우 "-t"옵션과 함께 pg_dump를 사용하여 테이블 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 [ [ 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에는 두 번째 방법을 사용하는 경우 테이블 스페이스 문제를 해결하기 위한 패치 가 있습니다.

---

postgres 테이블이 있습니다. 일부 데이터를 삭제해야합니다.

나는 추측한다 ...

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.

Look into pg_dump and pg_restore. Using pg_dump with some clever options and perhaps editing the output before pg_restoring might do the trick.

---

Since you are doing "what if"-type analysis on the data, I wonder if might you be better off using views.

You could define a view for each scenario you want to test based on the negation of what you want to exclude. I.e., define a view based on what you want to INclude. E.g., if you want a "window" on the data where you "deleted" the rows where X=Y, then you would create a view as rows where (X != Y).

Views are stored in the database (in the System Catalog) as their defining query. Every time you query the view the database server looks up the underlying query that defines it and executes that (ANDed with any other conditions you used). There are several benefits to this approach:

1. You never duplicate any portion of your data.
2. The indexes already in use for the base table (your original, "real" table) will be used (as seen fit by the query optimizer) when you query each view/scenario. There is no need to redefine or copy them.
3. Since a view is a "window" (NOT a shapshot) on the "real" data in the base table, you can add/update/delete on your base table and simply re-query the view scenarios with no need to recreate anything as the data changes over time.

There is a trade-off, of course. Since a view is a virtual table and not a "real" (base) table, you're actually executing a (perhaps complex) query every time you access it. This may slow things down a bit. But it may not. It depends on many issues (size and nature of the data, quality of the statistics in the System Catalog, speed of the hardware, usage load, and much more). You won't know until you try it. If (and only if) you actually find that the performance is unacceptably slow, then you might look at other options. (Materialized views, copies of tables, ... anything that trades space for time.)

---

Create a new table using a select to grab the data you want. Then swap the old table with the new one.

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

---

A simple way is include all:

CREATE TABLE new_table (LIKE original_table INCLUDING ALL);

참고URL : https://stackoverflow.com/questions/198141/copy-a-table-including-indexes-in-postgres