Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 903429FA1DE for ; Wed, 4 Oct 2006 15:03:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 17983-03-2 for ; Wed, 4 Oct 2006 18:02:40 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.180]) by postgresql.org (Postfix) with ESMTP id 5067E9FA13E for ; Wed, 4 Oct 2006 15:02:40 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so360176pyd for ; Wed, 04 Oct 2006 11:02:39 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=M8aPBqq6N02hzlGYFG1wZOIaY+7ibpz9AG66PR3Ud5CpGjHavOG4wICbIOCpv/xvySXwmh5SsysD1xQxCxAm44qg9HfVkv0Vku16V5edZByaAQ26ogt20ivL8KuNgdqWi6+omGPwQGRHdObcm91Wjkdd0ADirPHPb3lH6t+UOg4= Received: by 10.35.114.16 with SMTP id r16mr1664155pym; Wed, 04 Oct 2006 11:02:38 -0700 (PDT) Received: by 10.35.78.6 with HTTP; Wed, 4 Oct 2006 11:02:38 -0700 (PDT) Message-ID: Date: Wed, 4 Oct 2006 13:02:38 -0500 From: "Aaron Bono" To: "Daryl Richter" Subject: Re: i have table Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_16984_18533423.1159984958279" References: X-Google-Sender-Auth: 9ed58913f6519ad3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/49 X-Sequence-Number: 26438 ------=_Part_16984_18533423.1159984958279 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/4/06, Daryl Richter wrote: > > On 10/4/06 12:20 PM, "Aaron Bono" wrote: > > > On 10/4/06, Erik Jones wrote: > >> > >> Aaron Bono wrote: > >>> On 10/4/06, *Erik Jones* > > >>> wrote: > >>> > >>> There is one non-SQL related reason that I like to be able to > order > >>> columns, at least the way they are displayed whenever the table is > >>> described: human comprehension. For example, I like to group all > >>> keys > >>> in a table before data, that includes primary as well as foreign > >>> keys. > >>> So, say I'm building on to an existing application and I need to > do > >> an > >>> ALTER TABLE on an existing table to add a foreign key to an > existing > >>> table. I'd like that key to be listed with the other keys, but > >>> presently that's not possible in a simple way and, to be honest, I > >>> usually just go without as the process you've described below is > too > >>> prone to user (human) error when dealing with live, sensitive data > >> for > >>> me to want to mess with it. > >>> > >>> > >>> Ah, but it is possible... if you use views. > >>> > >>> I recommend you build views and query off them. Then you can control > >>> the order the columns will appear. > >> Which would be great if I didn't have (many) thousands of lines of code > >> that already use the tables. Besides, this is no where near a 'make or > >> break' thing. It's just a matter of aesthetic preference. > > Ah, but it *is* a "make or break thing." I have seen more than one > application crash because some developer didn't understand that columns in > a > relation (table) have no defined order. > > This (along with its sister axiom that rows have no defined order) is one > of > the most commonly misunderstood aspects of relational databases. > > > > > > > > > So do it as needed and convert your application slowly. > > > > I just name my views as table_name_vw so all you have to do is modify > your > > queries to hit the _vw instead of just the table. That shouldn't take > much > > time to refactor. I wasn't condoning using select * in your application - if an application is riddled with that, it is time to start fixing the problem because it WILL bite you eventually. I never let my team put select * in any queries that end up in the application code - bad stuff that! Of course sometimes, especially when you are doing quick throw away queries, select * is nice and controlling the order is handy. It is also beneficial when using a generic database tool which will almost definitely do a select *. ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_16984_18533423.1159984958279 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/4/06, Daryl Richter <daryl@eddl.us> wrote:
On 10/4/06 12:20 PM, "Aaron Bono" <postgresql@aranya.com> wrote:

> On 10/4/06, Erik Jones <erik@myemma.com> wrote:
>>
>> Aaron Bono wrote:
>>> On 10/4/06, *Erik Jones* <erik@myemma.com <mailto:erik@myemma.com>>
>>> wrote:
>>>
>>>     There is one non-SQL related reason that I like to be able to order
>>>     columns, at least the way they are displayed whenever the table is
>>>     described:  human comprehension.  For example, I like to group all
>>>     keys
>>>     in a table before data, that includes primary as well as foreign
>>>     keys.
>>>     So, say I'm building on to an existing application and I need to do
>> an
>>>     ALTER TABLE on an existing table to add a foreign key to an existing
>>>     table.  I'd like that key to be listed with the other keys, but
>>>     presently that's not possible in a simple way and, to be honest, I
>>>     usually just go without as the process you've described below is too
>>>     prone to user (human) error when dealing with live, sensitive data
>> for
>>>     me to want to mess with it.
>>>
>>>
>>> Ah, but it is possible... if you use views.
>>>
>>> I recommend you build views and query off them.  Then you can control
>>> the order the columns will appear.
>> Which would be great if I didn't have (many) thousands of lines of code
>> that already use the tables.  Besides, this is no where near a 'make or
>> break' thing.  It's just a matter of aesthetic preference.

Ah, but it *is* a "make or break thing."  I have seen more than one
application crash because some developer didn't understand that columns in a
relation (table) have no defined order.

This (along with its sister axiom that rows have no defined order) is one of
the most commonly misunderstood aspects of relational databases.

>
>
>
> So do it as needed and convert your application slowly.
>
> I just name my views as table_name_vw so all you have to do is modify your
> queries to hit the _vw instead of just the table.  That shouldn't take much
> time to refactor.

 
I wasn't condoning using select * in your application - if an application is riddled with that, it is time to start fixing the problem because it WILL bite you eventually.  I never let my team put select * in any queries that end up in the application code - bad stuff that!

Of course sometimes, especially when you are doing quick throw away queries, select * is nice and controlling the order is handy.  It is also beneficial when using a generic database tool which will almost definitely do a select *.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_16984_18533423.1159984958279--