SQL：SELECT除某些列外的所有列

SELECT * -the_geom FROM segments;

#1 楼

这样的功能在Postgres和SQL Standard（AFAIK）中都不存在。我认为这是一个非常有趣的问题，因此我在Google上搜索了一下，并在postgresonline.com上看到了一篇有趣的文章。

SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = 'officepark' 
            AND  c.column_name NOT IN('officeparkid', 'contractor')
    ), ',') || ' FROM officepark As o' As sqlstmt

您可以创建一个执行类似功能的函数。在邮件列表中也讨论了这些主题，但是总体共识几乎是相同的：查询模式。我敢肯定还有其他解决方案，但我认为它们都将涉及某种解决方案。魔术模式-queriying-foo。

BTW：请谨慎使用SELECT * ...，因为这可能会导致性能下降

#2 楼

真正的答案是您实际上无法做到。数十年来，这一直是要求的功能，开发人员拒绝实现它。

流行的建议查询模式表的答案将无法有效运行，因为Postgres优化器将动态函数视为黑盒（请参见下面的测试用例）。这意味着将不会使用索引，也不会智能地执行连接。如果使用诸如m4之类的宏系统，您会更好。至少它不会使优化器感到困惑（但仍可能使您感到困惑。）如果不亲自编写代码并编写功能或使用编程语言界面，就不会感到困惑。

我写了一个简单的证明下面的概念显示了在plpgsql中执行非常简单的动态执行会导致性能下降。还要注意，下面我必须强制一个将通用记录返回到特定行类型并枚举列的函数。因此，除非您要为所有表重新制作此功能，否则此方法不适用于“全选，但”。

test=# create table atest (i int primary key);
CREATE TABLE
test=# insert into atest select generate_series(1,100000);
INSERT 0 100000

test=# create function get_table_column(name text) returns setof record as
$$
    declare r record;
    begin
    for r in execute 'select  * from ' ||  loop
    return next r;
    end loop;
    return; 
    end; 
$$ language plpgsql; 

test=# explain analyze select i from atest where i=999999;
                                                      QUERY PLAN                                    
----------------------------------------------------------------------------------------------------
-------------------
 Index Only Scan using atest_pkey on atest  (cost=0.29..8.31 rows=1 width=4) (actual time=0.024..0.0
24 rows=0 loops=1)
   Index Cond: (i = 999999)
   Heap Fetches: 0
 Planning time: 0.130 ms
 Execution time: 0.067 ms
(5 rows)

test=# explain analyze
    select * from get_table_column('atest') as arowtype(i int) where i = 999999;
                                                        QUERY PLAN                                  
----------------------------------------------------------------------------------------------------
-----------------------
 Function Scan on get_table_column arowtype  (cost=0.25..12.75 rows=5 width=4) (actual time=92.636..
92.636 rows=0 loops=1)
   Filter: (i = 999999)
   Rows Removed by Filter: 100000
 Planning time: 0.080 ms
 Execution time: 95.460 ms
(5 rows)

您可以看到函数调用扫描了整个表，而直接查询使用了索引（95.46 ms vs. 00.07ms。）任何需要以正确的顺序使用索引或联接表的复杂查询。

#3 楼

实际上，从9.4开始引入JSONB的PostgreSQL确实有可能。我正在考虑如何在Google Map中显示所有可用属性（通过GeoJSON）的类似问题。

在irc频道上的johto建议尝试从JSONB中删除元素。

这是一个想法

select the_geom,
  to_jsonb(foo) - 'the_geom'::text attributes
from (
  select * from
  segments
) foo

虽然您得到json而不是单个列，但这正是我想要的。也许json可以扩展回单独的列。

#4 楼

您可以（不说应该这样做）的唯一方法是使用动态sql语句。很容易（就像DrColossos所写的那样）查询系统视图并查找表的结构并构建适当的语句。表结构？

#5 楼

我刚刚发现了一个解决方法，但是它需要从R内部发送SQL查询。它可能对R用户有用。

基本上dplyr软件包发送SQL（特别是PostgreSQL）查询并接受-(column_name)参数。

因此，您的示例可以编写如下：

select(segments, -(the_geom))

#6 楼

您从不会在SQL-VIEWS中看到* ...在\d any_view处检查psql。内部表示有一个（自省的）预处理。


这里的所有讨论都表明，问题建议（隐含在问题和讨论中）是程序员的语法糖，而不是真正的“ SQL优化问题” ...嗯，我的猜测是，这适用于80％的程序员。

因此可以实现为“带有自省的预分析” ...查看使用SELECT *声明SQL-VIEW时PostgreSQL所做的事情：VIEW构造函数将*转换为所有列的列表（通过自省，并在您运行CREATE VIEW源代码时进行。）

CREATE VIEW和PREPARE的实现

这是一个可行的实现。假设表t带有字段(id serial, name text, the_geom geom)。

CREATE VIEW t_full AS SELECT * FROM t;
-- is transformed into SELECT id,name,the_geom FROM t;

CREATE VIEW t_exp_geom AS SELECT * -the_geom FROM t;
-- or other syntax as EXCEPT the_geom
-- Will be transformed into SELECT id,name FROM t;

与PREPARE语句相同。 80％的程序员需要使用PREPARE和VIEWS的语法糖！


注意：当然，可行的语法可能不是- column_name，如果PostgreSQL中存在一些冲突，那么我们建议EXCEPT column_name，EXCEPT (column_name1, column_name2, ..., column_nameN)或其他。

#7 楼

在注释中，您解释说，这样做的目的是为了不显示具有较长内容的列的内容，而不是不显示列本身：


……有时我想查询带有几何列的表，而不会显示很长的几何字符串，这会使输出混乱。我不想指定所有列，因为可能有几十个列。


这是可能的，借助于一个辅助函数，该函数用null（任何在我的示例中为text列，但您可以针对要抑制的类型进行修改）：

create table my_table(foo integer, bar integer, baz text);

insert into my_table(foo,bar,baz) values (1,2,'blah blah blah blah blah blah'),(3,4,'blah blah');

select * from my_table;

foo | bar | baz                          
--: | --: | :----------------------------
  1 |   2 | blah blah blah blah blah blah
  3 |   4 | blah blah                    

create function f(ttype anyelement) returns setof anyelement as
$$
declare
  toid oid;
  tname text;
  nname text;
  cols text;
begin
  --
  select pg_type.oid, pg_namespace.nspname, pg_type.typname
  into toid, nname, tname
  from pg_type join pg_namespace on pg_namespace.oid=pg_type.typnamespace
  where pg_type.oid=pg_typeof(ttype);
  --
  select string_agg((case when data_type<>'text' 
                          then column_name 
                          else 'null::'||data_type||' "'||column_name||'"' end)
                   ,', ' order by ordinal_position)
  into cols
  from information_schema.columns 
  where table_schema=nname and table_name=tname;
  --
  return query execute 'select '||cols||' from '||nname||'.'||tname;
  --
end
$$ language plpgsql;

select * from f(null::my_table);

foo | bar | baz 
--: | --: | :---
  1 |   2 | null
  3 |   4 | null

dbfiddle此处

#8 楼

如上所述，动态地是唯一的答案，但我不建议这样做。如果从长远来看要添加更多列，但又不一定是该查询所必需的呢？

您将开始拉出比所需更多的列。

如果选择是插入内容的一部分，如

插入tableA（col1，col2，col3 .. coln）
选择除2列以外的所有内容
FROM tableB

该列匹配将出错，并且您的插入操作将失败。

这是可能的，但是我仍然建议为几乎每个列都需要的每个选择写入每个所需的列。

#9 楼

如果您的目标是通过不显示具有较大数据值的列来消除调试时屏幕上的混乱情况，则可以使用以下技巧：

（如果尚未安装“ hstore” contrib程序包，请安装拥有它：“ CREATE EXTENSION hstore;”）

对于带有col1，col2，col3的表“ test”，可以在显示之前将“ col2”的值设置为null：

或者，在显示前将两列设置为null：

select (r).* from (select (test #= hstore('col2',null)) as r from test) s;

警告是“测试”必须是一个表（一个别名或子选择无效），因为必须定义输入到hstore的记录类型。

#10 楼

从应用程序的角度来看，这是一个懒惰的解决方案。应用程序不太可能自动知道如何处理新列。
数据浏览器应用程序可能会查询元数据中的数据，并从正在运行的查询中排除列，或者选择列数据的子集。添加时可以排除新的BLOB。可以根据需要选择特定行的BLOB数据。


在支持动态查询的任何SQL变体中，都可以使用对表元数据的查询来构建查询。为了您的意图，我将根据类型而不是名称排除列。

#11 楼

这是我选择所有期望的列的功能。我结合了来自postgresonline.com和postgresql tuturial以及其他来源的想法。

CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32),
lastname VARCHAR(32), address VARCHAR(64));
INSERT INTO phonebook(phone, firstname, lastname, address) 
VALUES ('+1 123 456 7890', 'John', 'Doe', 'North America'), 
('+1 321 456 7890', 'Matti', 'Meikeläinen', 'Finland'), 
('+1 999 456 7890', 'Maija', 'Meikeläinen', 'Finland'), 
('+9 123 456 7890', 'John', 'Doe', 'Canada'), 
('+1 123 456 7890', 'John', 'Doe', 'Sweden'), 
('+1 123 456 7890', 'John', 'Doe2', 'North America');

drop function all_except_one(text,text);
CREATE OR REPLACE FUNCTION all_except_one(to_remove TEXT, table_name1 TEXT) 
RETURNS void AS $$

 DECLARE 
 rec_row RECORD;
 curs1 refcursor ;

 BEGIN
  --print column names:
  raise notice '%', ('|'|| ARRAY_TO_STRING(ARRAY(SELECT 
  COLUMN_NAME::CHAR(20) FROM INFORMATION_SCHEMA.COLUMNS WHERE
  TABLE_NAME=table_name1 AND COLUMN_NAME NOT IN (to_remove) ), 
  '|') ||'|') ; 

  OPEN curs1 FOR
  EXECUTE 'select table_1  from (SELECT ' || ARRAY_TO_STRING(ARRAY(
  SELECT COLUMN_NAME::VARCHAR(50) FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME=table_name1 AND COLUMN_NAME NOT IN (to_remove)    
  ), ', ') || ' FROM ' || table_name1 || ' limit 30)   table_1 ';

  LOOP
  -- fetch row into the rec_row
  FETCH curs1 INTO rec_row;

  -- exit when no more row to fetch
  EXIT WHEN NOT FOUND;

  -- build and print the row output

  raise notice '%',(select'| '|| regexp_replace( array_to_string(
  array_agg(a::char(20)),'|'),'["\(.*\)]+',   '','g') ||'|'  from 
  unnest(string_to_array(replace(replace(replace(trim(rec_row::text,
  '()'),'"',''), ', ','|'),')',' '),',')) as a);

  END LOOP;

  -- Close the cursor

  CLOSE curs1;

  END; $$ LANGUAGE plpgsql;

select  all_except_one('phone','phonebook');

--output:
--NOTICE:  |firstname           |lastname            |address             |
--NOTICE:  | John               |Doe                 |North America       |
--NOTICE:  | Matti              |Meikeläinen         |Finland             |
--NOTICE:  | Maija              |Meikeläinen         |Finland             |
--NOTICE:  | John               |Doe                 |Canada              |
--NOTICE:  | John               |Doe                 |Sweden              |
--NOTICE:  | John               |Doe2                |North America       |
-- all_except_one 
-- ----------------
-- (1 row)