Thread: Select all fields except one

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/28/07 11:18, Vivek Khera wrote:
>
> On Nov 28, 2007, at 11:06 AM, Matt Doughty wrote:
>
>> Is there a way of selecting all fields except for one in particular?
>> I'd like to create a query that says something like:
>>
>> select * except fieldx
>>
>>
>
> For best practices, you should never use select * in your queries.  You
> will inevitably end up with code that cannot deal with a schema change,
> and for any live system, you will have a schema change at some point...

Remember back in the late-80s when Data Dictionaries were the rage?
 (Some legacy still have these.)  3GL structs/copybooks can be
auto-generated from the CDD, so any added columns are auto-added to
your record structure.

Of course, you still have to rebuild your apps.

> It is best to explicitly list the field names your code is expecting.
> Besides, I don't think you can do what you want to do with just SQL.

- --
Ron Johnson, Jr.
Jefferson LA  USA

%SYSTEM-F-FISH, my hovercraft is full of eels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTaTaS9HxQb37XmcRAiaWAJ9/BiarNsC9UUNyreg8LiIq9+mUKwCeNS/L
1y4DkS4vJbJd15ZbPuwalac=
=QZG7
-----END PGP SIGNATURE-----

On Wed, Nov 28, 2007 at 05:06:45PM +0100, Matt Doughty wrote:
> Hi,
>
> Is there a way of selecting all fields except for one in particular?
> I'd like to create a query that says something like:
>
> select * except fieldx
>
> What I'm hoping to achieve is a query that can be valid for a number
> of different tables, although the field I don't want to select is
> always called the same (field_not_wanted, for example).

This sounds like self-modifying code.  I'd submit that you're trying
to do the wrong thing in the first place, and that you should look
over your design for flaws like this and re-do that design.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 28 Nov 2007 09:37:43 -0800
David Fetter <david@fetter.org> wrote:

> On Wed, Nov 28, 2007 at 05:06:45PM +0100, Matt Doughty wrote:
> > Hi,
> > 
> > Is there a way of selecting all fields except for one in particular?
> > I'd like to create a query that says something like: 
> > 
> > select * except fieldx 
> > 
> > What I'm hoping to achieve is a query that can be valid for a number
> > of different tables, although the field I don't want to select is
> > always called the same (field_not_wanted, for example).
> 
> This sounds like self-modifying code.  I'd submit that you're trying
> to do the wrong thing in the first place, and that you should look
> over your design for flaws like this and re-do that design.

Or to actually try to be helpful, you could consider using a server
side function instead:

select * from global_query({},TEXT);

Where the array list is is the list of fields you don't want selected
and the second parameter is the table you are going to use. Then you
just have to build the logic inside the function.

Sincerely,

Joshua D. Drake


> 
> Cheers,
> David.


- -- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTamnATb/zqfZUUQRArcrAJwIfarEnOPTzE8nzoWOfocW2C1kyQCgm4iU
e6DgUTvJD3bJDarJkoVpk7Y=
=GO+V
-----END PGP SIGNATURE-----

On Nov 28, 2007 12:18 PM, Vivek Khera <khera@kcilink.com> wrote:
> For best practices, you should never use select * in your queries.  You will
> inevitably end up with code that cannot deal with a schema change, and for
> any live system, you will have a schema change at some point...
>
> It is best to explicitly list the field names your code is expecting.
> Besides, I don't think you can do what you want to do with just SQL.

sometimes this is desirable.  for example, you may have a view defined
as 'select * from foo' which you want to change with foo...and it is
not too difficult to write application code that is tolerant of adding
extra fields.  also it is often useful to expand row and type
variables with '*' although this is kind of a postgresql peculiarity.

also consider 'insert...select' or 'create table as select' statements
that copy data from copy of table to another that by definition take
all the fields...so wouldn't a * be preferable to adjusting the field
list when it changes?

you are generally right though...i'm just laying out some excpetions.

merlin

On Wed, Nov 28, 2007 at 09:47:19AM -0800, Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wed, 28 Nov 2007 09:37:43 -0800
> David Fetter <david@fetter.org> wrote:
>
> > On Wed, Nov 28, 2007 at 05:06:45PM +0100, Matt Doughty wrote:
> > > Hi,
> > >
> > > Is there a way of selecting all fields except for one in
> > > particular?  I'd like to create a query that says something
> > > like:
> > >
> > > select * except fieldx
> > >
> > > What I'm hoping to achieve is a query that can be valid for a
> > > number of different tables, although the field I don't want to
> > > select is always called the same (field_not_wanted, for
> > > example).
> >
> > This sounds like self-modifying code.  I'd submit that you're
> > trying to do the wrong thing in the first place, and that you
> > should look over your design for flaws like this and re-do that
> > design.
>
> Or to actually try to be helpful, you could consider using a server
> side function instead:
>
> select * from global_query({},TEXT);

Helping somebody shoot themselves in the foot isn't helping them.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 28 Nov 2007 10:14:52 -0800
David Fetter <david@fetter.org> wrote:


> > > This sounds like self-modifying code.  I'd submit that you're
> > > trying to do the wrong thing in the first place, and that you
> > > should look over your design for flaws like this and re-do that
> > > design.
> > 
> > Or to actually try to be helpful, you could consider using a server
> > side function instead:
> > 
> > select * from global_query({},TEXT);
> 
> Helping somebody shoot themselves in the foot isn't helping them.

You have zero idea of his business requirements. He "may" have a valid
reason for this. I will grant that in this particular case it is
unlikely and that he is probably over thinking the automation of his
solution but still...

I would note that I use self modifying code all the time with
partitioning and there is *nothing* wrong with that. It calls "dynamic".

Sincerely,

Joshua D. Drake

> 
> Cheers,
> David.


- -- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTbH8ATb/zqfZUUQRAuDqAKCOKq4xoqn3lqZfYtxcYaF+z46ZMACeJd7D
UAKcBZDhQxBu8+lBsv8ZU18=
=BJBD
-----END PGP SIGNATURE-----

Seems odd that you'd know specifically which column you don't want, but
not know what columns you do want. And then not care what order those
desired columns happen to be be returned in.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Fetter
Sent: Wednesday, November 28, 2007 1:15 PM
To: Joshua D. Drake
Cc: Matt Doughty; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Select all fields except one

On Wed, Nov 28, 2007 at 09:47:19AM -0800, Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wed, 28 Nov 2007 09:37:43 -0800
> David Fetter <david@fetter.org> wrote:
>
> > On Wed, Nov 28, 2007 at 05:06:45PM +0100, Matt Doughty wrote:
> > > Hi,
> > >
> > > Is there a way of selecting all fields except for one in
> > > particular?  I'd like to create a query that says something
> > > like:
> > >
> > > select * except fieldx
> > >
> > > What I'm hoping to achieve is a query that can be valid for a
> > > number of different tables, although the field I don't want to
> > > select is always called the same (field_not_wanted, for
> > > example).
> >
> > This sounds like self-modifying code.  I'd submit that you're
> > trying to do the wrong thing in the first place, and that you
> > should look over your design for flaws like this and re-do that
> > design.
>
> Or to actually try to be helpful, you could consider using a server
> side function instead:
>
> select * from global_query({},TEXT);

Helping somebody shoot themselves in the foot isn't helping them.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/