在 PostgreSQL 中重命名多个列

问题描述

我的表中有一堆列格式如下:

My table has a bunch of columns in the following format:

_settingA
_settingB
_settingB

我想简单地重命名它们以添加如下前缀:

And I want to rename them simply to add a prefix as follows:

_1_settingA
_1_settingB
_1_settingC

我有 lot 超过三列要以这种方式重命名.如果我只有三个，我就一个一个地手动完成.

I have a lot more than three columns to rename in this way. If I had just three, I'd just do it manually one by one.

实现这一目标最快/最有效的方法是什么?

What is the quickest / most efficient way to achieve this?

推荐答案

没有单一的命令方法.显然你可以自己为 RENAME 输入多个命令，但让我来介绍一些改进:) 正如我在 这个答案

There's no single command aproach. Obviously you could type multiple comands for RENAME by your self, but let me intoduce some improvement:) As I said in this answer

...对于所有此类批量管理操作，您可以使用 PostgreSQL 系统表为您生成查询，而不是手动编写它们

在你的情况下是:

SELECT
    'ALTER TABLE ' || tab_name || ' RENAME COLUMN '
    || quote_ident(column_name) || ' TO '
    || quote_ident( '_1' || column_name) || ';'
FROM (
    SELECT
        quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name,
        column_name
    FROM information_schema.columns
    WHERE
            table_schema = 'schema_name'
            AND table_name = 'table_name'
            AND column_name LIKE '\_%'
) sub;

这将为您提供一组字符串，这些字符串是 SQL 命令，例如:

That'll give you set of strings which are SQL commands like:

ALTER TABLE  schema_name.table_name RENAME COLUMN "_settingA" TO "_1_settingA";
ALTER TABLE  schema_name.table_name RENAME COLUMN "_settingB" TO "_1_settingB";
...

如果您的表在 public 模式中，则无需在 WHERE 子句中使用 table_schema.还记得使用函数 quote_ident() -- 阅读我的原始答案以获得更多解释.

There no need using table_schema in WHERE clause if your table is in public schema. Also remember using function quote_ident() -- read my original answer for more explanation.

我已经更改了我的查询，现在它适用于名称以下划线_开头的所有列.因为下划线是 SQL 模式匹配中的特殊字符，我们必须对其进行转义(使用 )才能准确找到它.

I've change my query so now it works for all columns with name begining with underscore _. Because underscore is special character in SQL pattern matching, we must escape it (using ) to acctually find it.

这篇关于在 PostgreSQL 中重命名多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！