By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,302 Members | 1,299 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,302 IT Pros & Developers. It's quick & easy.

how to import data into table

P: n/a
How does one import data INTO a table. I am forced to read files (\i)
w/ a ton of insert statements. I am trying to import approx 800 lines
into the table. However, I must bundle them into a series of files
because of the bug in PGSQL (the buffer overflows). Interestingly,
the literature claims that the one feature of PostgreSQL is SPEED.
HA! Reading a single file w/ all the same inserts into MySQL takes
literally a fraction of a second. Reading the series of files into
PostgreSQL takes several minutes. Pls advise. Otherwise I am forced
to use MySQL. Thx.

PS. I am using v 6.5.3 which ships w/ RedHat 6.2
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
John Kolvereid wrote:
How does one import data INTO a table. I am forced to read files (\i)
w/ a ton of insert statements. I am trying to import approx 800 lines
into the table. However, I must bundle them into a series of files
because of the bug in PGSQL (the buffer overflows). Interestingly,
the literature claims that the one feature of PostgreSQL is SPEED.
HA! Reading a single file w/ all the same inserts into MySQL takes
literally a fraction of a second. Reading the series of files into
PostgreSQL takes several minutes. Pls advise. Otherwise I am forced
to use MySQL. Thx.

PS. I am using v 6.5.3 which ships w/ RedHat 6.2

If you have a single file with all of the inserts in it, use it. From
your postgres home directory use:
psql -d myDataBase -a -f myInsertFile

The -a parameter echos the results to the screen. If your data file is
just data (ie no SQL) use the COPY command from your home directory.

hth
Ron

BTW you should upgrade. There have been a lot of improvements in the
database since 6.5.3.

Jul 19 '05 #2

P: n/a
Hi Ron,
Thx for the reply. Here's what happened. My db is named 'odin'
and my text file is 'mails.out'. I Tried the following:
psql -d odin -a -f mails.out
response: Connection to database 'mails.out' failed.
psql odin -a -f mails.out
response: Connection to database 'postgres' failed.
It was not until I omitted the '-a' until it worked. And it did!.
However, I would like to shut off the echos as you indicated the -a
would do. That does not seem possible.
Also, I had tried the COPY command before. I tried it from
within a session and got the following:
odin=> copy dem from '/pub/mails/mails.tab';
ERROR: pg_atoi: error in "Mark": can't parse "Mark"
The tab-delimited file is '/pub/mails/mails.tab' and the 1st line is:
Mark Addington *@focus-asia.com
Let's hope you are right:
'There have been a lot of improvements in the database since 6.5.3.'

Thx for getting back. So far I am very unimpressed w/ PostgreSQL.

rstp <rs**@linuxwaves.com> wrote in message news:<3F**************@linuxwaves.com>...
John Kolvereid wrote:
How does one import data INTO a table. I am forced to read files (\i)
w/ a ton of insert statements. I am trying to import approx 800 lines
into the table. However, I must bundle them into a series of files
because of the bug in PGSQL (the buffer overflows). Interestingly,
the literature claims that the one feature of PostgreSQL is SPEED.
HA! Reading a single file w/ all the same inserts into MySQL takes
literally a fraction of a second. Reading the series of files into
PostgreSQL takes several minutes. Pls advise. Otherwise I am forced
to use MySQL. Thx.

PS. I am using v 6.5.3 which ships w/ RedHat 6.2

If you have a single file with all of the inserts in it, use it. From
your postgres home directory use:
psql -d myDataBase -a -f myInsertFile

The -a parameter echos the results to the screen. If your data file is
just data (ie no SQL) use the COPY command from your home directory.

hth
Ron

BTW you should upgrade. There have been a lot of improvements in the
database since 6.5.3.

Jul 19 '05 #3

P: n/a
Hi John,

Check out the "COPY" command. Its in the Reference section of the
PostgreSQL documentation. I usually find that I need to be logged in as
the Postgresql super user in order to get it to work.

P.S.: if MySQL works better for you, why don't you just use it?

Cheers,
w.k.

Q: How many Zen masters does it take to screw in a light bulb?
A: None. The universe spins the bulb and the Zen master stays out
of the way.

On Wed, 20 Aug 2003, John Kolvereid wrote:
How does one import data INTO a table. I am forced to read files (\i)
w/ a ton of insert statements. I am trying to import approx 800 lines
into the table. However, I must bundle them into a series of files
because of the bug in PGSQL (the buffer overflows). Interestingly,
the literature claims that the one feature of PostgreSQL is SPEED.
HA! Reading a single file w/ all the same inserts into MySQL takes
literally a fraction of a second. Reading the series of files into
PostgreSQL takes several minutes. Pls advise. Otherwise I am forced
to use MySQL. Thx.

PS. I am using v 6.5.3 which ships w/ RedHat 6.2

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.