postgresql SELECT * 除了第 n 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29274425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT * except nth column
提问by Kokizzu
Is it possible to SELECT *but without n-thcolumn, for example 2nd?
例如，是否可以SELECT *不使用n-th列2nd？
I have some view that have 4 and 5 columns (each has different column names, except for the 2nd column), but I do not want to show the second column.
我有一些视图有 4 列和 5 列（每列都有不同的列名，除了第二列），但我不想显示第二列。
SELECT * -- how to prevent 2nd column to be selected?
FROM view4
WHERE col2 = 'foo';
SELECT * -- how to prevent 2nd column to be selected?
FROM view5
WHERE col2 = 'foo';
without having to list all the columns (since they all have different column name).
无需列出所有列（因为它们都有不同的列名）。
回答by Nadeem_MK
The real answer is that you just can not practically (See LINK). This has been a requested feature for decades and the developers refuse to implement it. The best practice is to mention the column names instead of *. Using *in itself a source of performance penalties though.
真正的答案是你几乎不能（见LINK）。几十年来，这一直是一个要求的功能，开发人员拒绝实施它。最佳做法是提及列名而不是*. *虽然使用本身是性能损失的来源。
However, in case you really need to use it, you might need to select the columns directly from the schema -> check LINK. Or as the below example using two PostgreSQL built-in functions: ARRAYand ARRAY_TO_STRING. The first one transforms a query result into an array, and the second one concatenates array components into a string. List components separator can be specified with the second parameter of the ARRAY_TO_STRINGfunction;
但是，如果您真的需要使用它，您可能需要直接从架构中选择列 -> 检查LINK。或者像下面使用两个 PostgreSQL 内置函数的例子：ARRAY和ARRAY_TO_STRING。第一个将查询结果转换为数组，第二个将数组组件连接为字符串。列表组件分隔符可以通过ARRAY_TO_STRING函数的第二个参数指定；
SELECT 'SELECT ' ||
ARRAY_TO_STRING(ARRAY(SELECT COLUMN_NAME::VARCHAR(50)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='view4' AND
COLUMN_NAME NOT IN ('col2')
ORDER BY ORDINAL_POSITION
), ', ') || ' FROM view4';
where strings are concatenated with the standard operator ||. The COLUMN_NAME data type is information_schema.sql_identifier. This data type requires explicit conversion to CHAR/VARCHAR data type.
其中字符串与标准运算符连接||。COLUMN_NAME 数据类型是 information_schema.sql_identifier。此数据类型需要显式转换为 CHAR/VARCHAR 数据类型。
But that is not recommended as well, What if you add more columns in the long run but they are not necessarily required for that query? You would start pulling more column than you need.
但这也不推荐，如果您从长远来看添加更多列但该查询不一定需要它们怎么办？你会开始拉出比你需要的更多的列。
What if the select is part of an insert as in
如果选择是插入的一部分，如
Insert into tableA (col1, col2, col3.. coln) Select everything but 2 columns FROM tableB
The column match will be wrong and your insert will fail.
列匹配将是错误的，您的插入将失败。
It's possible but I still recommend writing every needed column for every select written even if nearly every column is required.
这是可能的，但我仍然建议为每个选择编写每个需要的列，即使几乎每个列都是必需的。
Conclusion:
结论：
Since you are already using a VIEW, the simplest and most reliable way is to alter you view and mention the column names, excluding your 2nd column..
由于您已经在使用 a VIEW，最简单和最可靠的方法是更改您的视图并提及列名称，不包括您的第二列。
回答by Antoine Reinhold Bertrand
-- my table with 2 rows and 4 columns
DROP TABLE IF EXISTS t_target_table;
CREATE TEMP TABLE t_target_table as
SELECT 1 as id, 1 as v1 ,2 as v2,3 as v3,4 as v4
UNION ALL
SELECT 2 as id, 5 as v1 ,-6 as v2,7 as v3,8 as v4
;
-- my computation and stuff that i have to messure, any logic could be done here !
DROP TABLE IF EXISTS t_processing;
CREATE TEMP TABLE t_processing as
SELECT *, md5(t_target_table::text) as row_hash, case when v2 < 0 THEN true else false end as has_negative_value_in_v2
FROM t_target_table
;
-- now we want to insert that stuff into the t_target_table
-- this is standard
-- INSERT INTO t_target_table (id, v1, v2, v3, v4) SELECT id, v1, v2, v3, v4 FROM t_processing;
-- this is andvanced ;-)
INSERT INTO t_target_table
-- the following row select only the columns that are pressent in the target table, and ignore the others.
SELECT r.* FROM (SELECT to_jsonb(t_processing) as d FROM t_processing) t JOIN LATERAL jsonb_populate_record(NULL::t_target_table, d) as r ON TRUE
;
-- WARNING : you need a object that represent the target structure, an exclusion of a single column is not possible
回答by Kelevandos
For columns col1, col2, col3and col4you will need to request
对于列col1、col2、col3和col4您需要请求
SELECT col1, col3, col4 FROM...
to omit the second column. Requesting
省略第二列。请求
SELECT *
will get you all the columns
会给你所有的列
