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