Postgres Pro
Downloads
Home   >   mailing lists

Thread: selecting multiple like-named columns

selecting multiple like-named columns

From
"Johnson, Shaunn"
Date:

Howdy:

Running PostgreSQL 7.2.1 on RedHat Linux 7.2.

Is there a way to do a select for the column
names from a table by using some type of
wild card?

Let's say I have something like column_1, column_2,
other_column_1, other_column_2 ...

[example]

select
column_%,
other_column_%,
from
t_table
;

[/example]

I'm trying to figure out how to get all of the
data from the columns that have similar
names.

Thanks!

-X

Re: selecting multiple like-named columns

From
David Link
Date:
You may have denormalized your data, for example if you have one table
like this:

date | chicago_sales | ny_sales | boston_sales | etc

It may be more convenient to store it like this:

date | city | sales

then you can say

  select sales from table where city = 'ny';

or what you want by not specifying a filter:

  select sales from table;

or

  select sum(sales) from table;
  select sum(sales) from table where city in ('ny', 'chicago');


There is only one reason you may want to keep it denormalized  .. and
that is for performance if the report you need looks like the original
table above.


--- "Johnson, Shaunn" <SJohnson6@bcbsm.com> wrote:
>
> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2.
>
> Is there a way to do a select for the column
> names from a table by using some type of
> wild card?
>
> Let's say I have something like column_1, column_2,
> other_column_1, other_column_2 ...
>
> [example]
>
> select
> column_%,
> other_column_%,
> from
> t_table
> ;
>
> [/example]
>
> I'm trying to figure out how to get all of the
> data from the columns that have similar
> names.
>
> Thanks!
>
> -X
>


__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com

Re: selecting multiple like-named columns

From
David Link
Date:
That said, if you want to do what you mentioned, you can do it
programmatically ...

my $select_list = join(",", map "${_}_sales", qw/chicago nyc boston/);

# That one-liner is the same as:
#   my $select_list = "";
#   my $comma = "";
#   for my $city ("chicago", "nyc", "boston") {
#    $select_list .= $comma . "$city" . "_sales";
#    $comma = ",";
#   }
# 'cause perl rocks the house.

#dynamic sql using DBI
my $SQL = $select_list . " from table";
my $sth = $dbh->prepare($SQL);
$sbh->execute;
for (@col = $sth->fetchrow_array) {
   print join (", ", @col), "\n";
}


--- David Link <dvlink@yahoo.com> wrote:
> You may have denormalized your data, for example if you have one
> table
> like this:
>
> date | chicago_sales | ny_sales | boston_sales | etc
>
> It may be more convenient to store it like this:
>
> date | city | sales
>
> then you can say
>
>   select sales from table where city = 'ny';
>
> or what you want by not specifying a filter:
>
>   select sales from table;
>
> or
>
>   select sum(sales) from table;
>   select sum(sales) from table where city in ('ny', 'chicago');
>
>
> There is only one reason you may want to keep it denormalized  .. and
> that is for performance if the report you need looks like the
> original
> table above.
>
>
> --- "Johnson, Shaunn" <SJohnson6@bcbsm.com> wrote:
> >
> > Howdy:
> >
> > Running PostgreSQL 7.2.1 on RedHat Linux 7.2.
> >
> > Is there a way to do a select for the column
> > names from a table by using some type of
> > wild card?
> >
> > Let's say I have something like column_1, column_2,
> > other_column_1, other_column_2 ...
> >
> > [example]
> >
> > select
> > column_%,
> > other_column_%,
> > from
> > t_table
> > ;
> >
> > [/example]
> >
> > I'm trying to figure out how to get all of the
> > data from the columns that have similar
> > names.
> >
> > Thanks!
> >
> > -X
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Finance - Get real-time stock quotes
> http://finance.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com