Re: R: Field's position in Table

----- Original Message -----
From: Adam Rich
To: Sam Mason ; vinny
Cc: pgsql-general(at)postgresql(dot)org
Sent: Monday, August 24, 2009 2:58 PM
Subject: Re: R: [GENERAL] Field's position in Table

From: vinny <vinny(at)xs4all(dot)nl>
Subject: Re: R: [GENERAL] Field's position in Table
To: "Sam Mason" <sam(at)samason(dot)me(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Date: Monday, August 24, 2009, 2:38 PM

On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote:
> On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote:
> > I can't really think of any real reason to put the field at a
> > particular position, applications don't reallty care about the order
> > of fields.
>
> Because it's very convenient for ad-hoc queries! PG currently assumes
> that the column order is the same as when it was created but there are
> (unimplemented) suggestions about how to "fix" this. See for example:
>
> http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php
>
> --
> Sam http://samason.me.uk/
>

But how is it convenient exactly, is it just a timesaver so you can
SELECT * instead of having to type SELECT firstname, lastname, email?

For me, saying all new fields must go at the end of the table is like saying all new functions must go at the end of your C source file. Not that it makes *any* difference to the end user, or other applications using your libraries, but as developers we tend to be more organized than the general public. Most programmers habitually organize their source code to keep related functions together. It seems sloppy to have 10 memory-related functions together in the source, and then an 11th hidden 6 pages down in the middle of file-related functions. And if you're writing OO code in C++ or Java, you even group private variables and methods separately from public ones. Most of the people who advocate tacking new fields at the end of a table would never dream of following this convention for source code.

So when I'm working in PgAdmin, I like to see my primary & foreign keys listed first, then data fields in logical groupings, and finally the standard "footer" fields we add to all tables like create & update by/date. Whenever I'm developing and need to reference a table definition, (or do a select * in pgAdmin for sample data) I lose productivity having to scan through all the fields repeatedly instead of seeing at a glance the fields I want because I know where they *should* be in the listing. Sometimes I have to scan through the fields several times before I finally see the one I want, because it was in the middle of unrelated items. I *never* code my applications to depend on field order; I'm referring to development convenience only.

(Just my two cents, YMMV, etc)

Just another two cents agreeing here. I think programmers tend to be a bit anal about this sort of thing. True, it makes no material difference but one just tends to be more comfortable with everything nicely organized.

Bayless