SQL 在 PostgreSQL 中选择具有特定列名的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15800367/
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 columns with particular column names in PostgreSQL
提问by Andrei Ivanov
I want to write a simple query to select a number of columns in PostgreSQL. However, I keep getting errors - I tried a few options but they did not work for me. At the moment I am getting the following error:
我想编写一个简单的查询来选择 PostgreSQL 中的一些列。但是，我不断收到错误消息 - 我尝试了一些选项，但它们对我不起作用。目前我收到以下错误：
org.postgresql.util.PSQLException: ERROR: syntax error at or near "column"
org.postgresql.util.PSQLException：错误：“列”处或附近的语法错误
To get the columns with values I try the followig:
要获取带有值的列，我尝试执行以下操作：
select * from weather_data where column like '%2010%'
Any ideas?
有任何想法吗？
回答by Erwin Brandstetter
columnis a reserved word. You cannot use it as identifier unless you double-quote it. Like: "column".
column是保留字。除非双引号，否则不能将其用作标识符。喜欢："column"。
Doesn't mean you should, though. Just don't use reserved words as identifiers. Ever.
但这并不意味着你应该这样做。只是不要使用保留字作为标识符。曾经。
To ...
到 ...
select a list of columns with 2010 in their name:
选择名称中带有 2010 的列列表：
.. you can use this function to build the SQL command dynamically from the system catalog table pg_attribute:
.. 您可以使用此函数从系统目录表 pg_attribute 动态构建 SQL 命令：
CREATE OR REPLACE FUNCTION f_build_select(_tbl regclass, _pattern text)
RETURNS text AS
$func$
SELECT format('SELECT %s FROM %s'
, string_agg(quote_ident(attname), ', ')
, )
FROM pg_attribute
WHERE attrelid =
AND attname LIKE ('%' || || '%')
AND NOT attisdropped -- no dropped (dead) columns
AND attnum > 0; -- no system columns
$func$ LANGUAGE sql;
Call:
称呼：
SELECT f_build_select('weather_data', '2010');
Returns something like:
返回类似：
SELECT foo2010, bar2010_id, FROM weather_data;
You cannot make this fully dynamic, because the return type is unknownuntil we actually build the query.
您不能使其完全动态化，因为在我们实际构建查询之前，返回类型是未知的。
回答by sgeddes
This will get you the list of columns in a specific table (you can optionally add schema if needed):
这将为您提供特定表中的列列表（如果需要，您可以选择添加架构）：
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'yourtable'
and column_name like '%2010%'
You can then use that query to create a dynamic sql statement to return your results.
然后，您可以使用该查询创建动态 sql 语句以返回结果。
回答by Craig Ringer
Attempts to use dynamic structures like this usually indicate that you should be using data formats like hstore, json, xml, etc that are amenible to dynamic access.
尝试使用动态结构，如这通常表明您应该使用的数据格式，如hstore，json，xml，等是amenible的动态访问权限。
You canget a dynamic column list by creating the SQL on the fly in your application. You can query the INFORMATION_SCHEMAto get information about the columns of a table and build the query.
您可以通过在应用程序中动态创建 SQL来获取动态列列表。您可以查询INFORMATION_SCHEMA以获取有关表列的信息并构建查询。
It's possible to do this in PL/PgSQL and run the generated query with EXECUTEbut you'll find it somewhat difficult to work with the result RECORD, as you must get and decode composite tuples, you can't expand the result set into a normal column list. Observe:
可以在 PL/PgSQL 中执行此操作并运行生成的查询，EXECUTE但是您会发现处理结果有些困难RECORD，因为您必须获取和解码复合元组，您无法将结果集扩展为普通列列表。观察：
craig=> CREATE OR REPLACE FUNCTION retrecset() returns setof record as $$
values (1,2,3,4), (10,11,12,13);
$$ language sql;
craig=> select retrecset();
retrecset
---------------
(1,2,3,4)
(10,11,12,13)
(2 rows)
craig=> select * from retrecset();
ERROR: a column definition list is required for functions returning "record"
craig=> select (r).* FROM (select retrecset()) AS x(r);
ERROR: record type has not been registered
About all you can do is get the raw record and decode it in the client. You can't index into it from SQL, you can't convert it to anything else, etc. Most client APIs don't provide facilities for parsing the text representations of anonymous records so you'll likely have to write this yourself.
您所能做的就是获取原始记录并在客户端对其进行解码。你不能从 SQL 索引到它，你不能把它转换成其他任何东西，等等。大多数客户端 API 不提供解析匿名记录的文本表示的工具，所以你可能必须自己编写它。
So: you canreturn dynamic records from PL/PgSQL without knowing their result type, it's just not particularly useful and it is a pain to deal with on the client side. You really want to just use the client to generate queries in the first place.
所以：你可以在不知道结果类型的情况下从 PL/PgSQL 返回动态记录，这不是特别有用，而且在客户端处理很痛苦。您真的只想首先使用客户端生成查询。
回答by Benj
Found this here:
在这里找到这个：
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
The result is a SQL SELECT query you just have to execute further. It fits my needs since I pipe the result in the shelllike this :
结果是您只需要进一步执行的 SQL SELECT 查询。它符合我的需要，因为我将结果通过管道传输到 shell 中，如下所示：
psql -U myUser -d myDB -t -c "SELECT...As sqlstm" | psql -U myUser -d myDB
That returns me the formatted output, but it only works in the shell. Hope this helps someone someday.
这会返回格式化的输出，但它只能在 shell 中工作。希望有一天这对某人有所帮助。
回答by Trinculo
You can't search all columns like that. You have to specify a specific column.
您不能像这样搜索所有列。您必须指定特定的列。
For example,
例如，
select * from weather_data where weather_date like '%2010%'
or better yet if it is a date, specify a date range:
或者更好的是，如果它是一个日期，请指定一个日期范围：
select * from weather_data where weather_date between '2010-01-01' and '2010-12-31'
