selecting multiple like-named columns

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

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