Обсуждение: How to initialize from flat files?

[ I posted this once already, but for some reason it never showed...]


What's the simplest way (through psql?) to initialize PostgreSQL
tables from data stored in flat files?  (In this case the data is
stored in several files consisting of newline-delimited records of
tab-delimited fields.)

Thanks!

kj

<kynn@panix.com> writes:
> What's the simplest way (through psql?) to initialize PostgreSQL
> tables from data stored in flat files?  (In this case the data is
> stored in several files consisting of newline-delimited records of
> tab-delimited fields.)

The COPY command should do what you want.  If the data contains any
tabs, newlines, or backslashes, you'll need to do a bit of preprocessing
to quote those characters; else you're good to go.

BTW, there is a difference between the SQL-level COPY command and psql's
\copy command.  With COPY the data file is read directly by the backend,
with \copy the file is read by psql and sent to the backend.  The COPY
method is faster when it works, but it doesn't work across machines or
when the data file is protected against access by the postgres user.

            regards, tom lane

tom lane writes:
> <kynn@panix.com> writes:
> > What's the simplest way (through psql?) to initialize PostgreSQL
> > tables from data stored in flat files?  (In this case the data is
> > stored in several files consisting of newline-delimited records of
> > tab-delimited fields.)

> The COPY command should do what you want.  If the data contains any
> tabs, newlines, or backslashes, you'll need to do a bit of preprocessing
> to quote those characters; else you're good to go.

> BTW, there is a difference between the SQL-level COPY command and psql's
> \copy command.  With COPY the data file is read directly by the backend,
> with \copy the file is read by psql and sent to the backend.  The COPY
> method is faster when it works, but it doesn't work across machines or
> when the data file is protected against access by the postgres user.

Thanks!

kj