Select columns with particular column names in PostgreSQL
database, dynamic-sql, postgresql, select, sql
Solution
`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:
.. 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;
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.
Problem
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: 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%' ``` Any ideas?