Re: creating a view from multiple tables (13 tables) - Mailing list pgsql-novice

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