User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Wed, 04 Oct 2006 12:37:57 -0400
Subject: Re: i have table
From: Daryl Richter <daryl@eddl.us>
To: <pgsql-sql@postgresql.org>
Message-ID: <C1495BA5.60EC%daryl@eddl.us>
Thread-Topic: [SQL] i have table
Thread-Index: Acbn03JlsQy7vFPGEduNkAAWy5/eVw==
In-Reply-To: <bf05e51c0610040920x329f3c79u9fdaed0d490f8bb1@mail.gmail.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit

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.
> 
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
>    http://codeelixir.com
> ==================================================================

--
Daryl
http://itsallsemantics.com