Thread

Re: [GENERAL] CVS Import/Export

Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-08-18T14:56:44Z

At 17:14 +0300 on 18/08/1999, Bruce Tong wrote:

> How do I import/export comma delimited tables?
>
> I thought a combination of pg_dump and psql might do it, but if so I must
> have missed it. I saw a mention of it for pgaccess, but I'm looking for
> something I can put in a shell script.

It has nothing to do with pgaccess. The way to import/export any tables is
using either the COPY command in PostgreSQL's SQL dialect, or the \copy
command in psql.

The difference between them is in where they look for the file to convert
to/from. The COPY command is executed by the backend, and looks for a file
in the backend's machine. The \copy looks on the client machine that runs
the psql. Since, more often than not, this is the same machine, the best
way to remember is that COPY is executed by the backend and therefore the
file must be readable to the postgres superuser (or writable for an
export), and \copy runs in the client, so it should be readable/writable to
the one who runs the psql.

COPY has an option to read the standard input instead of a file, which is
how clients like psql are able to write things like \copy. You can use COPY
FROM STDIN in shell scripts.

COPY is better that \copy as it allows you to set a delimiter, which \copy
does not - it always expects tabs.

Anyway, this imports data from a file named "stam.txt" into the table
"test5" of the database "testing":

psql -c 'COPY test5 FROM stdin' testing < stam.txt

The following exports the same table:

psql -qc 'COPY test5 TO stdin' testing > stam.txt

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma