Thread

1. Question on populating tables . . .

   Peter E. Chen <pchen3@jhmi.edu> — 2002-01-02T20:43:07Z

   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
   
   
   

2. Re: Question on populating tables . . .

   Doug McNaught <doug@wireboard.com> — 2002-01-02T21:06:30Z

   "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
   
   

3. Re: Question on populating tables . . .

   Bryan White <bryan@arcamax.com> — 2002-01-02T21:08:20Z

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

4. Re: Question on populating tables . . .

   Jeffrey W. Baker <jwbaker@acm.org> — 2002-01-02T21:16:12Z

   
   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
   
   
   

5. Re: Question on populating tables . . .

   Andrew Gould <andrewgould@yahoo.com> — 2002-01-02T21:16:35Z

   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
   
   

6. Re: Question on populating tables . . .

   Josh Rovero <rovero@sonalysts.com> — 2002-01-02T21:25:19Z

   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
   ***********************************************************************