[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 72652 77 14 15 Fax: +34 945 23 03 40 www.geograma.com image001.jpg
Re: [GENERAL] 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.
Re: [GENERAL] 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- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Select all fields except one
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 72652 77 14 15 Fax: +34 945 23 03 40 www.geograma.com image001.jpg
Re: [GENERAL] Select all fields except one
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 [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Select all fields except one
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 28 Nov 2007 09:37:43 -0800
David Fetter [EMAIL PROTECTED] 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-
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Select all fields except one
On Nov 28, 2007 12:18 PM, Vivek Khera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
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/
Re: [GENERAL] Select all fields except one
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 28 Nov 2007 10:14:52 -0800
David Fetter [EMAIL PROTECTED] 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-
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Select all fields except one
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
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/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
