[postgis-users] Except

[Chander Ganesan]

Barend Köbben wrote:
> Auw!
>
> That's what a all a nasty hack...
> What's wrong with just doing a
> SELECT thing _want_want1,..., thing _want_n
> And not include the thing_you_dont_want in the list...?
>   
I'm just answering the question that was asked :-) .  The other way to 
do it would be to something like

create table t2 as select * from t1 where 1 <> 1;
alter table t2 drop column no_view_col;
alter table t1 inherits t2;
select * from t2;

Arguably, both this and my previous solution have the same issues...but 
should work about the same way.

As a side note, I'm not saying I do (or don't) advocate either 
method..I'm just proposing a solution to the problem presented.  I'm not 
sure that there is a better one (aside from writing the query that you 
need and using it).  The advantage here is that both solutions I've 
mentioned meet the outlined constraints - we only know the column to be 
omitted, not the other columns...

I'd have to dig deeper, but there also might be a way to designate that 
column a system column (which would make it invisible perhaps) much like 
the ctid column...  Of course, this could be just as bad as the other 
solutions proposed...if not worse.

Chander
> __ 
> Barend Köbben
> International Institute for Geo-information
> Sciences and Earth Observation (ITC)
> PO Box 6, 7500AA Enschede (The Netherlands)
> ph: +31 (0)53 4874253; fax: +31 (0)53 4874335
>
>
>
> On 28-11-2007 23:07, "Chander Ganesan" <chander at otg-nc.com> wrote:
>
>   
>> Matt Doughty wrote:
>>     
>>> Hi,
>>>
>>>  
>>>
>>> Is there a way of selecting all fields except one in particular? I'd
>>> like to create a query that says something like: select * except
>>> field01.  The thing is the query needs to be valid for a number of
>>> different tables, each with a different set of fields apart from one
>>> (gid), which I don't want to select.
>>>
>>>       
>> Well, you could do the following:
>>
>> begin transaction;
>> alter table table_name drop column field01;
>> select * from table_name;
>> rollback;
>>
>> I think that should work.  Keep in mind that you wouldn't want to commit
>> the transaction, or you would lose the column data.
>>
>> Don't try this in other databases, PostgreSQL's DDL is transaction
>> safe...so you can do such things.
>>
>> Hope that helps
>>     
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


-- 
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Expert PostgreSQL Training

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071128/95cca405/attachment.html>