CODEWITHSUNDEEP
sqldatabasepostgresqlselectdynamic-sql
Andrei Ivanov
Andrei Ivanov

Reputation: 311

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?

Upvotes: 14

Views: 45289

Answers (5)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656646

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.

Upvotes: 16

Benj
Benj

Reputation: 1204

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.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324435

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.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

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.

Upvotes: 7

Trinculo
Trinculo

Reputation: 2021

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'

Upvotes: 0

Related Questions