How to SELECT ALL EXCEPT some columns in a table

People have asked us on several occasions if there is such a construct SELECT * EXCEPT(...list) FROM sometable. Sadly we do not think such a thing exists in the ANSI SQL Specs nor in PostgreSQL.

The above feature would come in handy when you have certain fields in your tables that are common across tables, but you need to leave them out in your query. A common case of this is when you have PostGIS tables loaded using shp2pgsql with a fields called gid and the_geom which are not terribly useful for simple data queries.

There are 2 common ways we use to achieve this result.

The PgAdmin III way

Here are the steps:

  1. Select the table you want from the tree.
  2. Right mouse-click
  3. Select Scripts->SELECT script
  4. This should pop up a SELECT .. script with all columns selected. Now just cut out the fields you don't want.
Pg Admin SELECT script


The Information Schema Hack Way


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 above for my particular example table - generates an sql statement that looks like this

SELECT o.officepark,o.owner,o.squarefootage 
    FROM officepark As o