从现有表中为SQL DDL语句过滤列名 [英] Filter column names from existing table for SQL DDL statement
问题描述
是否可以在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:
-
构建语句:
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屋!