Re: How to do mass upload - Mailing list pgsql-novice

On Wed, 14 Sep 2005 15:36:27 -0700 (PDT)
Srinivas Iyyer <srini_iyyer_bio@yahoo.com> threw this fish to the penguins:
> I have two different types of data. this data is
> processed to derive a set of values.
>
> I am trying to store the data in a database using
> postgres.
>
> I have designed two tables for two different types of
> raw data.  I designed one another table for storing
> the processed values from both raw data tables.
>
> Example:
> Raw_T_A is Raw data table A
> Raw_T_B is raw data table B
> Proc_T is processed data table
>
> Raw_T_A
> A_Ind_No.  colA  colB  colC  colD  colE
>
> Raw_T_B
> B_Ind.No   colA  colB  colC  colD  colE
>
>
> Proc_T
> Proc_Ind.No  XX   YY   colA  colB  colD
>
>
> Question:
> What is the better way of the design so that if I
> query  Proc_T i get to know the data row comes from
> Raw_T_A or Raw_T_B.   If I have to fill XX for
> A_Ind_No.  and YY to B_Ind.No, what is the better way.
>
>
> I am confused.

A more explicit example would be helpful for people to answer
your question, e.g. perhaps you mean something like:

create table Raw_T_A(colA int, colB int, colC text, colD text, colE int)
create table Raw_T_B(colA int, colB int, colC text, colD text, colE int)
create table Proc_T(XX int, YY int, colA int, colB int, colD text)

Then show a few rows from "select * from Raw_T_A", and "select * from Raw_T_B" and the expected corresponding contents
ofProc_T. 

I have no idea what your Ind_No columns are either.

Please repost a more clear example.  We would be glad to
help you if we can understand your problem.

[second question response below]

>
>
> Question 2:
> Previously for small database, to upload massive data
> into tables, I wrote a Python script to take each data
> from various tables and made to write SQL statement.
> Because, each data file had more than 100K lines,
> incorporating data into those tables took hell lot of
> time.
>
> I know that COPY statement lets one to upload easily,
> however, unfortunately it works only as super user.
>
> Although I have user rights to create a table and
> database, I could not use COPY.
>
> Is there any other way to use COPY or any other SQL
> command similar to COPY that a user can use instead of
> admin guy.  ADMIN refuses to hear my request.

You haven't told us what platform you're on, or what
interface you're using (please do this in future).

If you're using the command-line psql, use the \copy command.
[that's a backslash followed by c-o-p-y]
This copies data from a local file that you can control.

Most postgres interfaces have a means to do this.
It actually uses the sql: "copy mytable from stdin;" so anything
that you can feed from standard-input can use this.

I hope this helps,
    George

> Can any one please please help me.
>
> Thank you
>
>
>
> In this context I have two questions:
>
>
>
>
>
> ______________________________________________________
> Yahoo! for Good
> Donate to the Hurricane Katrina relief effort.
> http://store.yahoo.com/redcross-donate3/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


--
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)