為串列列複製具有單獨序列的表

在創建列管理工具時，我遇到了在 PostgreSQL 中快速復製表的需求，因此我不會使用非測試表測試新工具。為了有效地測試我最終打算在表格上使用的新列工具，parts我創建了這個新工具來複製parts，所以我最終會得到一個parts1表格。當我以為我終於解決了所有問題時，當列工具刪除表時遇到以下錯誤：

錯誤：無法刪除表部分，因為其他對象依賴於它詳細資訊：表部分的預設值列 id 取決於序列parts_id_seq1

我花了一天的大部分時間來解決這個問題，所以簡而言之，我可以簡單地使用字元串函式來重命名SEQUENCE_NAME變數以將parts表與parts表分離，還是比這更複雜？這是查詢：

DO $$
 DECLARE
   SEQUENCE_NAME VARCHAR;
 BEGIN
   SELECT s.relname INTO SEQUENCE_NAME
   FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid 
   INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid 
   INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) 
   INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace 
   WHERE s.relkind = 'S' 
   AND n.nspname = 'public' 
   AND t.relname='parts';

   LOCK TABLE parts;
   CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
   INSERT INTO parts1 SELECT * FROM parts;
   PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
 END;
$$ LANGUAGE plpgsql;

要創建盡可能接近的副本，您必須使用**INCLUDING ALL**with，CREATE TABLE .. (LIKE ..)因為可以有任意數量的列具有您顯然想要複製的預設值。

您只希望serial列獲得自己的獨立序列，這很有意義，並且可能應該是預設行為。現在，這應該可以完成工作：

複製任何給定表的功能

使用新的給定名稱和獨立serial列（如果有）複製任何給定的表（必須存在）。

數據不包括在內，複製它也很簡單。

CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
 RETURNS void AS
$func$
DECLARE
  _sql text;
BEGIN

-- Copy table
EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);

-- Fix serial columns, if any    
SELECT INTO _sql
      string_agg('CREATE SEQUENCE ' || seq, E';\n') || E';\n'
   || string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I'
                       , seq, _newtbl, a.attname), E';\n') || E';\n'
   || 'ALTER TABLE ' || quote_ident(_newtbl) || E'\n  '
   || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
                                , a.attname, seq), E'\n, ')
FROM   pg_attribute  a
JOIN   pg_attrdef    ad ON ad.adrelid = a.attrelid
                      AND ad.adnum   = a.attnum
    , quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq  -- new seq name
WHERE  a.attrelid = _tbl
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND    ad.adsrc = 'nextval('''
        || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
        || '''::regclass)'
;

IF _sql IS NOT NULL THEN
  EXECUTE _sql;
END IF;

END
$func$  LANGUAGE plpgsql VOLATILE;

稱呼：

SELECT f_copy_tbl('tbl', 'tbl1');

生成並執行以下形式的 SQL 程式碼：

CREATE TABLE tbl1 (LIKE tbl INCLUDING ALL);
-- only if there are serial columns:
CREATE SEQUENCE tbl1_tbl_id_seq;     -- one line per serial type ..
CREATE SEQUENCE "tbl1_Odd_COL_seq";  -- .. two in this example
ALTER SEQUENCE tbl1_tbl_id_seq OWNED BY tbl1.tbl_id;
ALTER SEQUENCE "tbl1_Odd_COL_seq" OWNED BY tbl1."Odd_COL";
ALTER TABLE tbl1
 ALTER tbl_id SET DEFAULT nextval('tbl1_tbl_id_seq'::regclass)
, ALTER "Odd_COL" SET DEFAULT nextval('"tbl1_Odd_COL_seq"'::regclass);

• 只有serial列有自己的序列。其他列預設值被原封不動地複制 - 包括nextval()從不屬於該列的序列或以任何方式與serial.
• 該函式對 SQL 注入是安全的，並且可以使用任意表和列名。

SQL小提琴。

對於該LIKE功能來說，為serial列創建單獨的序列將是一個有用的選項。您可能想向pgsql-general發布友好的功能請求。

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