PostgreSQL: SELECT all fields, filter some

Question

Hi,

In one of our databases, there is a table with dozens of columns, one of which is a geometry column.

I want to SELECT rows from the table, with the geometry transformed to another SRID. I want to use something like:

`SELECT *`

in order to avoid:

SELECT col_a, col_b, col_c, col_d, col_e, col_f, 
       col_g, col_h, transform(the_geom, NEW_SRID), ..., col_z

Any ideas?

Adam

Answer 1

SELECT 
  *, -- get all
  transform(the_geom, NEW_SRID) -- special case
FROM
  ...

Answer 2

So you're problem is the length of the SELECT-clause in the query? Well I guess you could consider doing:

SELECT *, transform(the_geom, NEW_SRID) as newsrid

which obviously is very short. However this means you're getting the column-value 'the_geom' twice for every row.

Edit: I want to stress though that I'm not saying that I like doing 'SELECT *'. No matter how many columns I select from a table, I always explicitly state the names. So that brings us to another question: 'What is the problem with explicitly naming all the columns?'.

Edit2: Okay so you have 60 columns in the table you're selecting from. I know it's a lot of typing work, but again, what's the problem? It's only a one-time issue. There are probably DB management programs that could even generate the query for you. At least the software we use can.

Using the wild card just isn't good practise, at least not in this situation. It's not easy to maintain, because in your programming environment you can't see a list of columns. You will always have to check that through a DB management program. And I hope you don't access the columns by index? Like:

object columnvalue = row[21];

Because that, in combination with the wild card, will make your program a real hell to maintain. Really, even if it costs you more time to program, in the end, naming the columns in your query will pay off.

Answer 3

Once you've selected a column, you can't make it go away again.

You could create a view exposing the data you're interested in, then run your query against that, but imho you're better off just explicitly naming the columns you want.

Answer 4

If you want to avoid typing the column names then something like

 SELECT array_to_string(array(SELECT CASE column_name WHEN 'the_geom' THEN 'transform(the_geom, NEW_SRID)' ELSE column_name END
 FROM information_schema.columns WHERE table_name = 'the_table'),E',\n');

will get you the list and modify the required column and then can be put into the select statement

Answer 5

select attname 
from pg_catalog.pg_attribute 
where not attisdropped 
    and attrelid=(select distinct tableoid from mytable);

will return the names of the columns in "mytable". I can't fathom any way to do the rest strictly in postgres, but you could write a function in whatever language you're using to join those columns for you except the ones you don't want and return your select string.

Or, I'd just use that statement to pull all the columns and use it to edit out the ones I want and write my query that way and get it over with. If you don't like a 50 line query sitting in the middle of your code, put it into a library so you don't have to see it.