Select all fields except one

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...

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.

-----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-----

There's probably some way to pull all the field names from the metadata
tables and build a query on-the-fly that selects all but the offensive
one.

________________________________

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Matt Doughty
Sent: Wednesday, November 28, 2007 11:07 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Select all fields except one

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). I've been told that an
array could work in this case but I'm not reallly sure about how to go
about this.

Cheers,

Matt

Matt Doughty

GEOGRAMA S.L.

Tel.: +34 945 13 13 72 652 77 14 15

Fax: +34 945 23 03 40

www.geograma.com

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/