在 PostgreSQL 中选择具有特定列名的列 [英] Select columns with particular column names in PostgreSQL

问题描述

我想编写一个简单的查询来选择 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:

org.postgresql.util.PSQLException:错误:语法错误在或附近列"

org.postgresql.util.PSQLException: ERROR: syntax error at or near "column"

要获取带有值的列，我尝试执行以下操作:

To get the columns with values I try the followig:

select * from weather_data where column like '%2010%'

有什么想法吗?

推荐答案

column 是一个 保留字.您不能将其用作标识符，除非您将其双引号.如:列".

但这并不意味着您应该这样做.只是不要使用保留字作为标识符.曾经.

Doesn't mean you should, though. Just don't use reserved words as identifiers. Ever.

到...

选择名称中包含 2010 的列列表:

select a list of columns with 2010 in their name:

.. 你可以使用这个函数从系统目录表动态构建 SQL 命令 pg_attribute:

.. you can use this function to build the SQL command dynamically from the system catalog table pg_attribute:

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;

调用:

SELECT f_build_select('weather_data', '2010');

返回类似:

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.

这篇关于在 PostgreSQL 中选择具有特定列名的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！