在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:错误:$ b $或$ $附近的语法错误bcolumn
org.postgresql.util.PSQLException: ERROR: syntax error at or near "column"
要获取具有值的列,请尝试使用followig:
To get the columns with values I try the followig:
select * from weather_data where column like '%2010%'
任何想法?
推荐答案
=http://www.postgresql.org/docs/current/interactive/sql-keywords-appendix.html =noreferrer> 保留字 。除非您双引号,否则不能将其用作标识符。像:
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:
..你可以使用这个功能从系统目录表pg_attribute动态建立SQL命令:
.. 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屋!