470,647 Members | 1,197 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

how to import data into table

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
3 44961
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
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
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.

Similar topics

10 posts views Thread by shumaker | last post: by
4 posts views Thread by Earl Anderson | last post: by
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.