Select columns with particular column names in PostgreSQL

Question

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?

Glume

Are you trying to return columns which contain 2010 in them or are you trying to return rows where a specific column has the value 2010 included? You can't select a list of columns like this (well, perhaps with dynamic sql you could)... – Eliseoelish

Do you have a column named column? – Asleep

Well, I am trying to return columns with 2010 as part of their name. For example there are columns with name: m01y2010 - so I want that, as well as : m02y2010 ... etc. - and values that stored in them columns. So there is no way of selecting a list of columns with 2010 in their name!? – Glume

no - there is no column called column – Glume

Dynamic SQL is probably your only option. – Eliseoelish

With column names like that, you might have a poor data model. I've heard good things about the book, Database Design for Mere Mortals. – Asleep

Closely related question: #8052940 – Inquiline

Answer 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:

.. 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.

Inquiline

Em, I actually didn't - I was just trying to search for column names with 2010 as part of their name, not the column called column with 2010 as part of one of the values... – Glume

Thanks! Used your solution! – Glume

How do I execute the select statement that this function returns? – Softener

@ian-campbell: You run it just like you ran the function call. You can automate it with dynamic SQL (random example: https://mcmap.net/q/28841/-dynamic-sql-execute-as-condition-for-if-statement) or with \gexec in psql (example https://mcmap.net/q/117326/-simulate-create-database-if-not-exists-for-postgresql). – Inquiline

Answer 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

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

Eliseoelish

Answer 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.

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.

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:

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.

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.

Kurtis

And by "somewhat difficult", you mean "next to impossible", since the return type is unknown. You would be required to provide a column definition list, but if you knew the column definition list, you wouldn't need the dynamic query to begin with. Catch 22. – Inquiline

@ErwinBrandstetter You can call a function that returns record or setof record without a column-definition-list if you're happy with an unexpanded anonymous record in the result-set. See updated answer. – Kurtis

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

Ratsbane

Answer 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 :

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.

Sportive