Dear Group,
I have 13 tables with duplication of elements and
14th tables that has all the unique elements of 13
tables. What I do not have in 14th table are columns
that are part of these 13 tables.
I wanted to male a view with all 14 tables.

My code looks like this:
CREATE VIEW affy_annotation AS
SELECT
affy_unique_probeset.affy_probeset_name,
fc_probe_set_id,
fc_aeneChip_array,
fc_species_scientific_name,
fc_annotation_date,
..........................,
..................
FROM
affy_unique_probeset,
affy_hc_g110,
affy_hg_focus,
affy_********,
affy_********,
*************,

WHERE affy_unique_probeset.affy_probeset_name =
fc_Probe_Set_ID;

=>\i /home/...../..../postgres/marray2/view.sql

psql:/home/../temp/postgres/marray2/view.sql:58:
ERROR: column reference "fc
_probe_set_id" is ambiguous

In my case every table (13 numbers) has 20 columns and
column names are identical.
In the select statement it is difficult to specify
every table name . column name and do this for 20
times for every chip.

So could any one let me know how to create a view from
multiple tables.






_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

You have to use the "table.column" notation. E.g.
"table12.fc_probe_set_id" instead of just "fc_probe_set_id"

Cheers,

Marc

You obviously have to; since all column names are identical how could
you hope PostgreSQL to be sure which table you mean?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

If you haven't already, you might consider a different structure for your
tables. Instead of the dozen or so tables, combine them all into only one
table, with the same columns as each of the dozen tables. Differentiate
between the "tables" by means of an additional column which designates the
"type" of each record. This new column would have a dozen different possible
values.

-----Original Message-----
From: M. Bastin [mailto:marcbastin (AT) mindspring (DOT) com]
Sent: Friday, September 24, 2004 3:04 PM
To: Kumar S
Cc: pgsql-novice (AT) postgresql (DOT) org
Subject: Re: [NOVICE] creating a view from multiple tables (13 tables)

You have to use the "table.column" notation. E.g.
"table12.fc_probe_set_id" instead of just "fc_probe_set_id"

Cheers,

Marc

You obviously have to; since all column names are identical how could
you hope PostgreSQL to be sure which table you mean?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Thank you for you suggestion. In fact, I have
identical columns and some of the tuples are also
identical (redundancy).

Each table (13) has more than 20K rows and some of
these rows are found in all 13 tables. To avoid
duplication of data across the tables I wanted to
create a table of unique elements for all these 13
tables and then refer this unique to all other tables
in my database. The most important aspect of these
rows are that the content of these rows are standard
and can never be changed. In case, if the company
comes up with new elements( rows) they will be
appended to my unique table.

The reason why I did not feel comfortable the idea
that is almost along your lines was:

1. There are thousands of rows and I wanted to avoid
thousands of insert commands.
2. The tuples are repeated across tables (that means a
row with element A can be found simulatenously in 10
tables). I thought it would a great deal of effort to
filter redundancy.

3. I DO NOT know by using 'COPY FROM' command, I can
upload unique elements for all the tables once.

Documentation talked about unique OIDs but not unique
tuples which did not make sense to me and I left.

This is the reason I chose to extract unique IDs from
all tables and then attach the other columns data to
these unique IDs using create view. Also, I do not
know if I can index VIEW instead of a table,

If you can suggest a way to make unique table using
COPY FROM command or any other way that will be a
great help.

Thank you,

Kumar.



--- "Garris, Nicole" <Nicole.Garris (AT) dof (DOT) ca.gov> wrote:




_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster