Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select columns with particular column names in 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: 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?

like image 578
Andrei Ivanov Avatar asked Apr 04 '13 00:04

Andrei Ivanov


People also ask

How do I get unique column values in PostgreSQL?

Removing duplicate rows from a query result set in PostgreSQL can be done using the SELECT statement with the DISTINCT clause. It keeps one row for each group of duplicates. The DISTINCT clause can be used for a single column or for a list of columns.

What is the wildcard in PostgreSQL?

Wildcards in PostgreSQL is used to find matching rows values from tables; it is also used to find matching patterns rows from tables, Wildcards is also used to find matching rows, column and tables names; the output of the wildcard operator will return matching name, which was table name, column name or rows, In ...


1 Answers

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.

like image 186
Erwin Brandstetter Avatar answered Sep 27 '22 21:09

Erwin Brandstetter