Обсуждение: 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 | |
|
Вложения
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
-----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/