How to SELECT ALL EXCEPT some columns in a table   Checkout our PostGIS in Action book 2nd Edition. First chapter is a free download

Postgres OnLine Journal

Blog View    Periodical View   PostGIS in Action   About the Authors   Consulting



Saturday, April 05. 2008


How to SELECT ALL EXCEPT some columns in a table

Printer Friendly

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.

  • Using PgAdmin's CREATE SELECT script feature. This exists in other GUI tools as well.
  • Using an Information Schema script hack to construct the SELECT statement

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
Posted by Leo Hsu and Regina Obe in beginner, pgadmin, postgis, q&a at 18:53 | Comments (33) | Trackbacks (0)

Trackbacks
Trackback specific URI for this entry

No Trackbacks


Comments
Display comments as (Linear | Threaded)

This is a neat hack, and an interesting example of SQL-generating SQL.

One little thing to add: when somebody asks you for a thing like this, what they're really doing is expressing pain over a denormalized schema, so it's good to take a broader look at that part, first chance you get.
#1 David Fetter (Homepage) on 2008-04-06 13:52 (Reply)
Good point. We do ask people first why they want to do that before we suggest a solution. Surprisingly a lot of people want to do this for one off things like this and use cases we never thought of.
I guess the point is just because you've never had a need for something, don't dismiss other people without hearing them out.

For us for example we have built custom query builders, where we want to allow admin users to query from all fields except for example big blob fields and so forth (e.g. geometry fields). This comes in handy in that regard too since you can exclude certain data types as well as named fields since the information_schema.columns table provides data type info.
#1.1 Regina on 2008-04-07 01:34 (Reply)
"SELECT * EXCEPT(...list) FROM sometable" :
Great syntax suggestion.
This actually is painfull to select all but some fields (as regina suggested, we are actually doing the exact same thing : exclude binary fields or large text fields).
Which means that we are actually doing it using two request : first one to get the columns of the table, then remove unwanted ones, then the real request.
With this, we could do all of this in 1 database access...
I would love that!
#2 Dricks on 2008-04-07 04:11 (Reply)
How do you do this query by someone who is not a superuser? I am getting permission errors if I am a public user
#3 Bruce on 2010-10-21 14:00 (Reply)
Which version of PostgreSQL are you running. That query should work even as a non-super user as long as the user has access to the table being filtered.

The information_schema.columns I believe is open to all with database access, but is set to filter to only list the tables and columns a user has access to.

I jsut tested with a non-superuser account and seemed to work fine for me on my 9.0 install.

Just to verify -- are you able to run a

SELECT * FROM information_schema.columns;

as your non-superuser?

That should return all the table columns the user has access to.
#3.1 Leo on 2010-10-22 22:08 (Reply)
Can this type of solution be applied in an Access query?
#4 Lanox on 2011-05-11 03:58 (Reply)
Lanox,

Not sure I understand. You mean with linked PostgreSQL tables or just access in general?

Access in general - well it wouldn't be the same. You can write a vb function in MS Access to loop thru the Msyscolumns or use the ADO class and build the SQL dynamically and overwrite a querydef.

Not sure if that is what you had in mind.
#4.1 Regina on 2011-05-12 03:56 (Reply)
For Oracle Users, Below are the steps :)
1.Right Click on Table
2.Select Browse Data menu
3.Uncheck the column which you want to exclude
4.Click on ok.

So simple rite ? Njoy :)
#5 Sangeetha Bhuvaneswaran (Homepage) on 2012-08-07 08:16 (Reply)

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA
 
 

Entry's Links

Quicksearch

Calendar

Back December '24 Forward
Mon Tue Wed Thu Fri Sat Sun
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          

Categories

All categories

Archives

Subscribe

  • XML

Blog Administration

Open login screen