Thread: creating a view from multiple tables (13 tables)
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
>psql:/home/../temp/postgres/marray2/view.sql:58: >ERROR: column reference "fc_probe_set_id" is ambiguous You have to use the "table.column" notation. E.g. "table12.fc_probe_set_id" instead of just "fc_probe_set_id" Cheers, Marc >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. You obviously have to; since all column names are identical how could you hope PostgreSQL to be sure which table you mean?
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@mindspring.com] Sent: Friday, September 24, 2004 3:04 PM To: Kumar S Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] creating a view from multiple tables (13 tables) >psql:/home/../temp/postgres/marray2/view.sql:58: >ERROR: column reference "fc_probe_set_id" is ambiguous You have to use the "table.column" notation. E.g. "table12.fc_probe_set_id" instead of just "fc_probe_set_id" Cheers, Marc >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. 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
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@dof.ca.gov> wrote: > 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@mindspring.com] > Sent: Friday, September 24, 2004 3:04 PM > To: Kumar S > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] creating a view from multiple > tables (13 tables) > > >psql:/home/../temp/postgres/marray2/view.sql:58: > >ERROR: column reference "fc_probe_set_id" is > ambiguous > > You have to use the "table.column" notation. E.g. > "table12.fc_probe_set_id" instead of just > "fc_probe_set_id" > > Cheers, > > Marc > > >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. > > 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 > _______________________________ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com