Thread: Multiple COPYs

Multiple COPYs

From
rasdj@frontiernet.net
Date:
Hello,

Having a great time with PG - ported an erp from oracle and db2. First
I tried MySql but choked somewhere in the 900 table region. I have a
python script to translate the syntax and it loads about 2000 tables.

Now I want to COPY my dumps - I have 1 data dump for each table. Any
tips on what to use so that I can read the file name into a variable
and pass it as the file name in the COPY command and have one script
load all my tables?

Thanks for any help.

Rasta DJ


Re: Multiple COPYs

From
Sean Davis
Date:
On Jun 16, 2005, at 12:32 PM, rasdj@frontiernet.net wrote:

> Hello,
>
> Having a great time with PG - ported an erp from oracle and db2. First
> I tried MySql but choked somewhere in the 900 table region. I have a
> python script to translate the syntax and it loads about 2000 tables.
>
> Now I want to COPY my dumps - I have 1 data dump for each table. Any
> tips on what to use so that I can read the file name into a variable
> and pass it as the file name in the COPY command and have one script
> load all my tables?

Why not use Python or a simple shell script to generate a file like:

\copy table1 from 'table1.txt'
\copy table2 from 'table2.txt'
....

And then do psql -f filename

Sean


Re: Multiple COPYs

From
Douglas McNaught
Date:
rasdj@frontiernet.net writes:

> Hello,
>
> Having a great time with PG - ported an erp from oracle and db2. First
> I tried MySql but choked somewhere in the 900 table region. I have a
> python script to translate the syntax and it loads about 2000 tables.
>
> Now I want to COPY my dumps - I have 1 data dump for each table. Any
> tips on what to use so that I can read the file name into a variable
> and pass it as the file name in the COPY command and have one script
> load all my tables?

Should be pretty easy from the shell, something like (untested):

for f in *.sql; do
  tablename=`basename $f .sql`
  psql -c "\\copy $tablename from $f" mydatabase
done

-Doug