Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.

Something like:

SELECT * -the_geom FROM segments;
  • I once heard that this functionality was deliberately excluded from the SQL standard because changing adding columns to the table will alter the query results. Is this true? Is the argument valid?
  • Is there a workaround, especially in PostgreSQL?
share|improve this question
add comment

5 Answers

up vote 20 down vote accepted

Such a feature does not exist in neither Postgres nor the SQL Standard (AFAIK). I think this is a quite interessting question so I googled a little bit and came accross a interessting article on postgresonline.com.

They show an approch that selects the columns directly from the schema:

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

You could create a function that does something like that. Such topics were also discussed on the mailinglists, but the overall consensus was pretty much the same: query the schema.

I'm sure that there are other solutions but I think they will all involve some kind of magic schema-queriying-foo.

BTW: Be carefull with SELECT * ... as this can have performance penalties

share|improve this answer
add comment

The only way you can (don't say you should) do that is by using dynamic sql statements. It's easy (like DrColossos wrote) to query the system views and find the structure of the table and build proper statements.

PS: Why would you want to select all/some columns without knowing/writing exactly your table structure?

share|improve this answer
    
Regarding your PS: Sometimes I want to query a table with geometric column, without displaying the very long geometry string which garbles the output. I don't want to specify all the columns, because there might be some dozens. –  Adam Matan Mar 29 '11 at 11:59
    
So only dynamic sql can save you from lot of typing :-). –  Marian Mar 29 '11 at 13:07
add comment

Dynamically as stated above is the only answer but I won't recommend it. What if you add more columns in the long run but they are not necessarily required for that query?

You would start pulling more column than you need.

What if the select is part of an insert as in

Insert into tableA (col1, col2, col3.. coln) Select everything but 2 columns FROM tableB

The column match will be wrong and your insert will fail.

It's possible but I still recommend writing every needed column for every select written even if nearly every column is required.

share|improve this answer
    
This approach is obviously programmatically wrong, but it is harmless and useful as a console query for SELECTs. –  Adam Matan Mar 29 '11 at 13:58
add comment
  • From an application perspective, this is a lazy solution. An application is unlikely to automatically know what to do with then new column(s).

    Data browser applications may query the metadata for the data and exclude the columns from the queries being run, or select a subset of the column's data. New BLOBs can be excluded when added. BLOB data for particular rows can be selected on demand.

  • In any SQL variant which supports dynamic queries, the query can be built using a query on the tables meta data. For your intent, I would exclude columns based on type rather than name.

share|improve this answer
add comment

If your goal is to remove clutter from the screen during debugging by not displaying columns with large data values, then you can use the following trick:

(install "hstore" contrib package if you don't already have it:"CREATE EXTENSION hstore;")

For a table "test" with col1,col2,col3, you can set the value of "col2" to null before displaying:

select (r).* from (select (test #= hstore('col2',null)) as r from test) s;

Or, set two columns to null before displaying:

select (r).* from (select (test #= hstore('col2',null) #= hstore('col1',null)) as r from test) s;

the caveats is that "test" has to be a table (an alias or subselect won't work) since the record type feeding into hstore must be defined.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.