postgresql 使用SELECT语句修改表

bvuwiixz  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(96)

我需要在我们的数据库中找到所有的列名称,并将它们更改为“”。不过,我在做这件事上遇到了麻烦。如有任何帮助,我们将不胜感激

ALTER TABLE (SELECT  quote_ident(c.table_schema) || '.'
  || quote_ident(c.table_name) || ' RENAME "' || c.column_name || '" TO ' || quote_ident(lower(c.column_name)) || ';' As ddlsql
  FROM information_schema.columns As c
  WHERE c.table_schema NOT IN('information_schema', 'pg_catalog') 
      AND c.column_name <> lower(c.column_name) 
  ORDER BY c.table_schema, c.table_name, c.column_name;);
kokeuurv

kokeuurv1#

我想你可以试试这个:

DO $$ 
DECLARE 
   ddlsql TEXT; 
BEGIN 
   FOR ddlsql IN (
      SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || '.'
      || quote_ident(c.table_name) || ' RENAME COLUMN "' || c.column_name || '" TO ' || quote_ident(lower(c.column_name)) || ';' 
      FROM information_schema.columns As c
      WHERE c.table_schema NOT IN('information_schema', 'pg_catalog') 
        AND c.column_name <> lower(c.column_name)
   )
   LOOP
      EXECUTE ddlsql;
   END LOOP;
END $$;

它应该自动生成并执行所有必需的ALTER TABLE语句。
请试着告诉

相关问题