Re: COPY performance

On Sat, 13 Apr 2002, Joe Conway wrote:

> Nigel J. Andrews wrote:
> >
> > I'm trying to COPY in a table of 1 million rows. The table is created by:
> >
> > create table chat_post_new (
> > session_id INT NOT NULL references chat_session (session_id),
> > poster_name VARCHAR(32) NOT NULL,
> > time TIMESTAMP NOT NULL,
> > post_number INT NOT NULL,
> > FTS txtidx
> > );
> >
> > The old definition had the integer columns as int2s and the FTS column wasn't
> > there. Because I wanted to reorder the rows, add them in a more natural order
> > (by time), I created the data file read in by the copy command using
> >
> > SELECT session_id || '\t' || poster_name || '\t' || time || '\t' || post_number
> > FROM chat_post
> > ORDER BY time
> >
> > After removing the first and last couple of lines, so that only the data is in
> > the file, renaming the original table and creating the new version I tried
> > running:
> >
> > COPY chat_post FROM 'file-path'
> >
>
> I'm not sure if this is your problem, but I believe you need the same
> number of columns in your file as your table. So if you want FTS to be
> null, append \N to the output, e.g.
>
> SELECT session_id || '\t' || poster_name || '\t' || time || '\t' ||
> post_number || '\t\N'
> FROM chat_post
> ORDER BY time
>

Ah, thanks for that tip. I don't know if it'll make a difference, I'll try
later, but I had wondered what that \N was doing at the end of a copy out to
file I did before.

> Also, you should probably leave the foreign key reference off the table
> and add it after you load the data.

I should have mentioned that I'm doing the copy in to the table in a
transaction block with all constraints deferred. That should mean it's only at
the commit stage that foreign key will be checked right?

> Hope this helps,

Well the \N is useful to know thanks.

>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants