Re: COPY performance

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

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

Hope this helps,

Joe