在PostgreSQL中选择具有特定列名称的列

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?

有任何想法吗？

5 个解决方案

#1

column is a reserved word. You cannot use it as identifier unless you double-quote it. Like: "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), ', ')
                 , $1)
    FROM   pg_attribute 
    WHERE  attrelid = $1
    AND    attname LIKE ('%' || $2 || '%')
    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 unknown until we actually build the query.

你不能使它完全动态，因为在我们实际构建查询之前，返回类型是未知的。

#2

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%'

SQL Fiddle Demo

SQL小提琴演示

You can then use that query to create a dynamic sql statement to return your results.

然后，您可以使用该查询创建动态sql语句以返回结果。

#3

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等。

You can get a dynamic column list by creating the SQL on the fly in your application. You can query the INFORMATION_SCHEMA to 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 EXECUTE but 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 can return 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返回动态记录，它只是没有特别有用，在客户端处理是一件痛苦的事。您真的只想使用客户端来生成查询。

#4

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'

#5

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 shell like 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。希望有一天能帮助某人。

#1