Postgresql

刪除具有給定名稱的所有列

  • September 3, 2014

我正在將 MSSQL 模式轉換為 PostgreSQL,並且在該模式中,大多數表都有一個名為TimestampMSSQLtimestamp數據類型的列,它是有效的rowversion

在將記錄插入這些表時,您不需要為時間戳列指定值,因為 MSSQL 會自動更新該列。我相信這與PG中的相同xmin

所以現在當我的應用程序嘗試插入表時,PG 會爆炸說它需要該Timestamp列的值。

因此,我正在考慮在導入模式後編寫一些東西,從我的表中刪除所有被呼叫Timestamp並具有timestamp數據類型的列。

我發現我可以從中獲取列和表資訊pgclasspgattribute但是我現在卡住瞭如何循環並進行檢查然後刪除列。有人可以指出我正確的方向嗎?

select
   t.relname,
   a.attname,
   d.typname
from
 pg_class t
 INNER JOIN pg_attribute a 
 on a.attrelid = t.oid
 INNER JOIN pg_type d
 on d.oid = a.atttypid
 where relkind='r' and attname = 'timestamp' and d.typname = 'timestamp'
ORDER BY t.relname

對於一次性使用,您不需要持久化函式。使用DO聲明:

DO
$do$
DECLARE
  rec record;
BEGIN
FOR rec IN
  SELECT a.attrelid::regclass::text AS tbl, a.attname
  FROM   pg_class c
  JOIN   pg_attribute a ON a.attrelid = c.oid
  WHERE  c.relkind = 'r'
  AND    a.attname = 'timestamp'
  AND    a.atttypid = 'timestamp'::regtype
  AND    a.attnum > 0
  AND    NOT a.attisdropped
LOOP
  RAISE NOTICE '%', format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
  -- Check test output before uncommenting EXECUTE!
  -- EXECUTE format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
END LOOP;
END
$do$;

要點

  • 如頂部所述,可能不需要函式。DO語句的主體是相同的,預設語言是 plpgsql。

  • 使用**FOR循環**的隱式游標。更簡單、更快捷。在 plpgsql 中很少需要顯式游標。

  • 表名在 Postgres 數據庫中**不是唯一的。**在多個模式中可以有任意數量的同名表。

  • 避免使用非標準(雙引號)標識符的SQL 注入和基本異常。

  • 我假設您知道timestamptimstamptz數據類型?這只會刪除數據類型為 的列timestamp [without time zone]

  • 用 標識數據類型a.atttypid = 'timestamp'::regtype,因此您不必加入pg_type。更簡單。

  • 排除系統列和死列:

AND a.attnum > 0 AND NOT a.attisdroppedand

好吧,我似乎讓它像這樣工作:

DROP FUNCTION somefuncname();
CREATE OR REPLACE FUNCTION somefuncname() RETURNS void LANGUAGE plpgsql AS $$
DECLARE
 tablename VARCHAR(500);
 columnname varchar(500);

 curs1 CURSOR FOR  select
   t.relname,
   a.attname,
   d.typname
   from
   pg_class t
   INNER JOIN pg_attribute a 
   on a.attrelid = t.oid
   INNER JOIN pg_type d
   on d.oid = a.atttypid
   where relkind='r' and attname = 'timestamp' and d.typname = 'timestamp'
   ORDER BY t.relname;

BEGIN
 FOR myrecordvar IN curs1 LOOP 
   RAISE NOTICE E'Table Name:%',myrecordvar.relname;
   EXECUTE 'ALTER TABLE "' || myrecordvar.relname || '" DROP COLUMN ' || myrecordvar.attname;
 END LOOP;
END
$$;
SELECT somefuncname();

引用自:https://dba.stackexchange.com/questions/75573