Re: COPY performance
От | Nigel J. Andrews |
---|---|
Тема | Re: COPY performance |
Дата | |
Msg-id | Pine.LNX.4.21.0204131959070.3278-100000@ponder.fairway2k.co.uk обсуждение исходный текст |
Ответ на | Re: COPY performance (Joe Conway <mail@joeconway.com>) |
Ответы |
Re: COPY performance
|
Список | pgsql-general |
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@postgresql.org) > -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
В списке pgsql-general по дате отправления: