Saturday, April 05. 2008How to SELECT ALL EXCEPT some columns in a tablePrinter FriendlyRecommended Books: PostGIS In Action PostgreSQL 8.4 Official The SQL Language PostgreSQL 8.4 Server Administration
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.
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.
"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!
How do you do this query by someone who is not a superuser? I am getting permission errors if I am a public user
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.
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.
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 :) |
Entry's LinksQuicksearchCalendar
Categories
Blog Administration |