Thread: Question on populating tables . . .

Hey All,

Is there a way to populate a table using a tab-delimited file?  This feature
is available in MySQL and I'd like to know if it is possible with
postgreSQL.

Thanks.

Peter

"Peter E. Chen" <pchen3@jhmi.edu> writes:

> Hey All,
>
> Is there a way to populate a table using a tab-delimited file?  This feature
> is available in MySQL and I'd like to know if it is possible with
> postgreSQL.

Read up on the SQL COPY statement.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

> Is there a way to populate a table using a tab-delimited file?  This
feature
> is available in MySQL and I'd like to know if it is possible with
> postgreSQL.

Yes, see the COPY command.  This is also the format used by pg_dump.

Yes, PostgreSQL can read a text file that uses ONE
delimiter.  I think the tab delimiter is the default.
Unfortunately, if you have more than one delimiter, or
if your text  values are enclosed in quotation marks,
you'll have to edit the data file prior to moving the
data.

Once the file is ready to go, create the table and use
the COPY command to insert the data.

Speed hint - If you're inserting a lot of data, you
may want to drop the table's index and recreate it
after inserting the data.

Best of luck,

Andrew Gould

--- "Peter E. Chen" <pchen3@jhmi.edu> wrote:
> Hey All,
>
> Is there a way to populate a table using a
> tab-delimited file?  This feature
> is available in MySQL and I'd like to know if it is
> possible with
> postgreSQL.
>
> Thanks.
>
> Peter
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com

On Wed, 2 Jan 2002, Peter E. Chen wrote:

> Hey All,
>
> Is there a way to populate a table using a tab-delimited file?  This feature
> is available in MySQL and I'd like to know if it is possible with
> postgreSQL.

Yes, you can copy a table to or from a file.  See the documentation for
the COPY command.

-jwb

Yes.  After you have defined the table (i.e., CREATE TABLE...)
you can use COPY (in psql or code) or \C (in psql).  Tabs are
the default delimiters for COPY.

  \h copy
Command:     COPY
Description: copy data between files and tables
Syntax:
COPY [ BINARY ] table [ WITH OIDS ]
     FROM { 'filename' | stdin }
     [ [USING] DELIMITERS 'delimiter' ]
     [ WITH NULL AS 'null string' ]
COPY [ BINARY ] table [ WITH OIDS ]
     TO { 'filename' | stdout }
     [ [USING] DELIMITERS 'delimiter' ]
     [ WITH NULL AS 'null string' ]

Something like:

    copy mytable from '/home/chen/testdata.tab';

ought to work.



Peter E. Chen wrote:

> Hey All,
>
> Is there a way to populate a table using a tab-delimited file?  This feature
> is available in MySQL and I'd like to know if it is possible with
> postgreSQL.
>
> Thanks.
>
> Peter
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>


--
P. J. "Josh" Rovero                                 Sonalysts, Inc.
Email: rovero@sonalysts.com    www.sonalysts.com    215 Parkway North
Work: (860)326-3671 or 442-4355                     Waterford CT 06385
***********************************************************************