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

7 Answers 7

up vote 26 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

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

There's a workaround I have just discovered, but it requires to send SQL queries from within R. It may be of use to R users.

Basically the dplyr package sends SQL (and specifically PostgreSQL) queries and accepts the -(column_name) argument.

So your example could be written as follows:

select(segments, -(the_geom))
share|improve this answer

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

The real answer is that you just can not practically. This has been a requested feature for decades and the developers refuse to implement it.

The popular answer suggesting querying the schema tables will not be able to run efficiently because the Postgres optimizer considers dynamic functions a black box (see the test case below). That means that indexes will not be used and joins will not be done intelligently. You would be much better off with some sort of macro system like m4. At least it will not confuse the optimizer (but it may still confuse you.) Without forking the code and writing the feature yourself or using a programming language interface you are stuck.

I wrote a simple proof of concept below showing how bad performance would be with a very simple dynamic execution in plpgsql. Notice also, that below I have to coerce a function returning a generic record into a specific row type and enumerate the columns. So this method will not work for 'select all but' unless you want to remake this function for all your tables.

test=# create table atest (i int primary key);
CREATE TABLE
test=# insert into atest select generate_series(1,100000);
INSERT 0 100000

test=# create function get_table_column(name text) returns setof record as
$$
    declare r record;
    begin
    for r in execute 'select  * from ' || $1 loop
    return next r;
    end loop;
    return; 
    end; 
$$ language plpgsql; 

test=# explain analyze select i from atest where i=999999;
                                                      QUERY PLAN                                    
----------------------------------------------------------------------------------------------------
-------------------
 Index Only Scan using atest_pkey on atest  (cost=0.29..8.31 rows=1 width=4) (actual time=0.024..0.0
24 rows=0 loops=1)
   Index Cond: (i = 999999)
   Heap Fetches: 0
 Planning time: 0.130 ms
 Execution time: 0.067 ms
(5 rows)

test=# explain analyze
    select * from get_table_column('atest') as arowtype(i int) where i = 999999;
                                                        QUERY PLAN                                  
----------------------------------------------------------------------------------------------------
-----------------------
 Function Scan on get_table_column arowtype  (cost=0.25..12.75 rows=5 width=4) (actual time=92.636..
92.636 rows=0 loops=1)
   Filter: (i = 999999)
   Rows Removed by Filter: 100000
 Planning time: 0.080 ms
 Execution time: 95.460 ms
(5 rows)

As you can see the function call scanned the whole table while the direct query used the index (95.46 ms vs. 00.07ms.) These kinds of functions would tank any kind of complicated query that needed to use indexes or join tables in the right order.

share|improve this answer

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

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.