从现有表中为SQL DDL语句过滤列名 [英] Filter column names from existing table for SQL DDL statement

查看:71
本文介绍了从现有表中为SQL DDL语句过滤列名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

是否可以在psql中对列名本身进行过滤?我想在一个单独的架构la(伪代码)中生成原始表的有限版本(具有几百列):

Is it possible to filter on column names themselves in psql? I want to generate a limited version of the original table (with several hundred columns) in a separate schema a la (pseudocode):

create table why.am_i_doing_this
    select *
    from original.table 
    where column_name_of_the_table not in ('column_1', 'column_2' );

推荐答案

动态构建DDL命令.您可以分两个步骤进行操作:

Build the DDL command dynamically. You can do it in two steps:

  1. 构建语句:

  1. Build statement:

SELECT 'CREATE TABLE why.am_i_doing_this AS SELECT '
    || string_agg(column_name, ', ' ORDER BY ordinal_position)
    || ' FROM original.table'
FROM   information_schema.columns
WHERE  table_schema = 'original'
AND    table_name = 'table'
AND    column_name NOT IN ('column_1', 'column_2');

  • (检查是否正常!),然后在第二次往返服务器的过程中执行生成的语句.

  • (Check it's good!) Then execute the generated statement in a second round trip to the server.

    这基于信息架构视图 information_schema.columns .或者,您可以使用 pg_catalog.pg_attribute .相关:

    This is based on the information schema view information_schema.columns. Alternatively, you could use pg_catalog.pg_attribute. Related:

    但是它也可以通过一次往返服务器来完成:

    But it can be done in a single round trip to the server, too:

    DO 只是用于临时执行PL/pgSQL代码的简单包装器.您可以在函数或过程中执行相同的操作.

    DO is just a simple wrapper for ad-hoc execution of PL/pgSQL code. You might do the same in a function or procedure.

    DO
    $$
    BEGIN
       EXECUTE (
       SELECT 'CREATE TABLE why.am_i_doing_this AS SELECT '
           || string_agg(column_name, ', ' ORDER BY ordinal_position)
           || ' FROM original.table'
       FROM   information_schema.columns
       WHERE  table_schema = 'original'
       AND    table_name = 'table'
       AND    column_name NOT IN ('column_1', 'column_2')
       );
    END
    $$;
    

    使用psql元命令 \ gexec

    更简单

    自从您提到默认的交互式终端 psql .在那里,您可以使用 \ gexec .它...

    将当前查询缓冲区发送到服务器,然后将查询输出的每一行的每一列(如果有的话)都视为要执行的SQL语句.

    Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as a SQL statement to be executed.

    所以:

    SELECT 'CREATE TABLE why.am_i_doing_this AS SELECT '
        || string_agg(column_name, ', ' ORDER BY ordinal_position)
        || ' FROM original.table'
    FROM   information_schema.columns
    WHERE  table_schema = 'original'
    AND    table_name = 'table'
    AND    column_name NOT IN ('column_1', 'column_2')\gexec
    

    这篇关于从现有表中为SQL DDL语句过滤列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

  • 查看全文
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆