selecting all columns but one

On Tue, Jul 28, 2009 at 9:12 AM, blackwater dev<blackwaterdev@gmail.com> wrote:

I have a table with a lot of columns.  One of the columns I want to alias so
have a query of:
select *, column as newname from mytable.
The problem is I now have column and newname in the results.  I don't want
to select column by column.  How can I do a select * but omit one?
Thanks!

there is no easy way. the only way I can think of is to build the
query with dynamic sql (either in pl/pgsql or the client), and query
the system catalogs so you can build the column list filtering out the
column you don't want.

merlin

blackwater dev, 28.07.2009 15:12:

I have a table with a lot of columns. One of the columns I want to
alias so have a query of:

select *, column as newname from mytable.

The problem is I now have column and newname in the results. I don't
want to select column by column. How can I do a select * but omit one?

You need to list all columns that you want explicitely.

Using SELECT * in a production application is considered bad practice anyway

Thomas

In response to blackwater dev :

I have a table with a lot of columns. �One of the columns I want to alias so
have a query of:

select *, column as newname from mytable.

The problem is I now have column and newname in the results. �I don't want to
select column by column. �How can I do a select * but omit one?

You can't.

The only way: ask information_schema.columns and build a string that
contains all except the special column and execute that string. (within
a function, plpgsql).

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net