將索引從一張表複製到另一張表

我有一系列 ETL 作業，我在其中swap使用CREATE TABLE table1_swap LIKE table1. 為了使填充table1_swap更快，我不包括索引。但是，當我完成載入後，我需要將這些索引重新應用到新填充的表中。這些索引是在這些 ETL 作業的範圍之外創建的，因此如果不需要，我寧願不必對CREATE INDEX呼叫進行硬編碼。

是否可以將一組索引從一個表“轉移”或“複製”到另一個表？

您可以從 Postgres 系統目錄中檢索每個索引的完整 DDL 語句。

以下將檢索CREATE單個表的所有索引語句：

select pg_get_indexdef(idx.oid)||';'
from pg_index ind
 join pg_class idx on idx.oid = ind.indexrelid
 join pg_class tbl on tbl.oid = ind.indrelid
 left join pg_namespace ns on ns.oid = tbl.relnamespace
where tbl.relname = 'your_table_name'
  and ns.nspname = 'your_table_schema';

您可以將輸出假離線到腳本中，並在複製數據並交換錶名後執行它。

基本

要獲取所有索引定義，您可以使用 @a_horse 已經提供的系統目錄pg_get_indexdef(index_oid)資訊功能。

regclass但是，使用參數查詢可以相當簡單。對於架構中呼叫的表tbl：public

SELECT pg_get_indexdef(indexrelid) || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;  -- optionally schema-qualified

這包括所有索引：PK、部分、功能、唯一、具有特殊運算符類等。

詳細資訊regclass：

• Postgres 函式中的 SQL 注入與準備好的查詢

準備 DDL 語句

你還不能使用它。您必須用新表名替換舊表名 - 並且您不想替換字元串中的誤報（如與表名匹配的列名）：

如果您使用Postgres 的預設命名約定，則索引定義如下所示：

CREATE INDEX **tbl**_**tbl**_id_idx ON **tbl** USING btree (**tbl**_id);

CREATE INDEX **tbl**_people_gin_idx ON **tbl**
USING gin (((data -> 'people'::text)) jsonb_path_ops);

CREATE INDEX **tbl**_comecol_nonull_idx ON **tbl**
USING btree (somecol) WHERE (cu**tbl**ade IS NOT NULL);

我將原始表名的每一次出現都加粗。請注意我們不想替換的三個誤報（列名或索引名的一部分）。

此查詢應該可以完美執行 - 但請自己對照您的實際索引進行驗證！

SELECT regexp_replace(regexp_replace(
         pg_get_indexdef(indexrelid)
       , 'tbl', 'tbl1')
       , ' ON tbl ', ' ON tbl1 ')
   || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;

第一個replace()僅替換名稱中的第一個匹配項（除非另有說明）。第二個regexp_preplace()更具體，只替換實際的表名。

對於給定的範例，您將獲得：

CREATE INDEX **tbl1**_tbl_id_idx ON **tbl1** USING btree (tbl_id);

CREATE INDEX **tbl1**_people_gin_idx ON **tbl1**
USING gin (((data -> 'people'::text)) jsonb_path_ops);

CREATE INDEX **tbl1**_somecol_nonull_idx ON **tbl1**
USING btree (somecol) WHERE (cutblade IS NOT NULL);

但是我們還沒有考慮非標準的表名、模式或search_path設置。所有這些都內置在下面的函式中。

全自動化

如果您對自己的命名方案有信心，您可以完全自動化：

CREATE OR REPLACE FUNCTION f_copy_idx(_tbl text, _tbl1 text
                                   , _sch text = 'public', _sch1 text = 'public')
 RETURNS void AS
$func$
DECLARE
  _full_tbl  text := format('%I.%I', _sch, _tbl);
  _full_tbl1 text := format('%I.%I', _sch1, _tbl1);
BEGIN
  -- RAISE NOTICE '%', -- for testing
  EXECUTE
  (SELECT string_agg(
             regexp_replace(regexp_replace(
                pg_get_indexdef(indexrelid)
              , _tbl,  _tbl1)
              , ' ON ' || _full_tbl || ' ',  ' ON ' || _full_tbl1 || ' ')
           , '; ') AS idx
   FROM   pg_index
   WHERE  indrelid = _full_tbl::regclass);
END
$func$ LANGUAGE plpgsql SET search_path = '';

這也適用於需要雙引號和不同模式的非標準表名。

為了強制使用模式限定的表名，我在函式 ( ) 中重置了 search_path SET search_path = ''。通過這種方式，我得到了不依賴於設置的可靠結果。

我添加'public'了架構參數的預設值_sch和_sch1. 因此，如果兩個表都位於公共模式中，您可以簡單地忽略模式參數。

稱呼：

SELECT f_copy_idx('mytbl', 'newtbl');  -- all in schema public

或者對於需要雙引號和不同模式的表名：

SELECT f_copy_idx('old_TBL', 'table', 'public', 'New_SCHEmA');

SQL Fiddle展示了正在使用的函式。

引用自：https://dba.stackexchange.com/questions/119873