copy command
Hi, all
PostgreSQL has two COPY commands to import/export data;
copy [binary] <class_name> [with oids]
{to|from} {<filename>|stdin|stdout} [using delimiters <delim>];
and...
\copy table {from | to} <fname>
both of them work in a different way;
In the first one you have to specify 'filename' surrounded by ''
and if you don't specify an absolute pathname PostgreSQL uses
$PGDATA/base/<databasename>/<filename>
In the last one you have to specify 'filename' without by ''
and if you don't specify an absolute pathname PostgreSQL uses
current working directory.
and last... if you don't specify any parameter it show me this:
java=> \copy
connecting to new database: opy
PQexec() -- There is no connection to the backend.
Could not connect to new database. exiting
My question is:
Why do we have two commands to doing the same operation ?
Why are they different ?
Thanks, Jose'
At 4:18 PM 4/28/98, Jose' Soares Da Silva wrote:
stuff about copy vs. \copy
In at least one instance, the reason for 2 versions is that copy is faster
(presumably) than \copy, but for some sort of security reason, \copy is
"safer" because it goes through the PostgreSQL backend.
At least, that's my understanding based on a message I got from psql about
me not having permission to use copy, so use \copy instead. [See mail from
a few days ago.]
Of course, \copy is the one that won't allow a delimiter other than \t
(tab), so that kinda screwed me over for awhile.
Thanks to some kind list members I got a Perl and a sed script to change my
delimiters, but it actually turned out that I could just change my
delimiter to \t (tab) in my export package or a text editor, once I was
told that \t (tab) was the default, which I couldn't find documented
anywhere (maybe I missed it...) I could even almost read the Perl script,
except for the regexp part.
So, suggestions for postgres hackers/documenters:
#1. Modify the docs to explicitly state that \t (tab) is the default delimiter.
#2. Modify the docs to explicitly state what form the argument to USING
DELIMITERS can take.
[Presumably just one character, but I didn't try it with multiple. I
*CAN'T* try it.]
#3. Modify \copy to match copy in syntax (IE include the delimiter stuff).
#4. Beef up the FAQ about importing tables and the copy command,
particularly for folks who can't use copy, and mention options such as
exporting as tab separated text or altering the separator charachter to \t
(tab).
--
--
-- "TANSTAAFL" Rich lynch@lscorp.com
Import Notes
Resolved by subject fallback
At 4:18 PM 4/28/98, Jose' Soares Da Silva wrote:
stuff about copy vs. \copy
In at least one instance, the reason for 2 versions is that copy is faster
(presumably) than \copy, but for some sort of security reason, \copy is
"safer" because it goes through the PostgreSQL backend.At least, that's my understanding based on a message I got from psql about
me not having permission to use copy, so use \copy instead. [See mail from
a few days ago.]Of course, \copy is the one that won't allow a delimiter other than \t
(tab), so that kinda screwed me over for awhile.Thanks to some kind list members I got a Perl and a sed script to change my
delimiters, but it actually turned out that I could just change my
delimiter to \t (tab) in my export package or a text editor, once I was
told that \t (tab) was the default, which I couldn't find documented
anywhere (maybe I missed it...) I could even almost read the Perl script,
except for the regexp part.So, suggestions for postgres hackers/documenters:
#1. Modify the docs to explicitly state that \t (tab) is the default delimiter.
Added.
#2. Modify the docs to explicitly state what form the argument to USING
DELIMITERS can take.
Done.
[Presumably just one character, but I didn't try it with multiple. I
*CAN'T* try it.]
#3. Modify \copy to match copy in syntax (IE include the delimiter stuff).
Added to TODO lst.
#4. Beef up the FAQ about importing tables and the copy command,
particularly for folks who can't use copy, and mention options such as
exporting as tab separated text or altering the separator charachter to \t
(tab).
Not sure about this one. Someone is working on a newbies FAQ.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)