Re: COPY command details - Mailing list pgsql-general
From | Benjamin Arai |
---|---|
Subject | Re: COPY command details |
Date | |
Msg-id | 460C18CA.4030304@araisoft.com Whole thread Raw |
In response to | Re: COPY command details (Bruce Momjian <bruce@momjian.us>) |
Responses |
Re: COPY command details
(Bruce Momjian <bruce@momjian.us>)
Re: COPY command details (Ron Johnson <ron.l.johnson@cox.net>) Re: COPY command details (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-general |
I would prefer not to drop the index because the database is several hundred gigs. I would prefer to incrementally add to the index. Benjamin Bruce Momjian wrote: > Benjamin Arai wrote: > >> So, is there a way to defer the index updating until a later period >> of time. More specifically, I would like to do several COPIES to a >> running database, then afterward force a update on the index via a >> vacuum or something similar. >> > > Sure, drop the index, do the COPY, and then recreate the index. That is > done often. > > --------------------------------------------------------------------------- > > > >> Benjamin >> >> On Mar 29, 2007, at 1:03 AM, A. Kretschmer wrote: >> >> >>> am Thu, dem 29.03.2007, um 0:13:09 -0700 mailte Benjamin Arai >>> folgendes: >>> >>>> Hi, >>>> >>>> If I have a PostgreSQL table with records and logical indexes already >>>> created, if I use COPY to load additional data, does the COPY update >>>> the indexes during, after, or not at all? >>>> >>> after, i think. >>> >>> test=# create table foo (id int primary key, name text); >>> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index >>> "foo_pkey" for table "foo" >>> CREATE TABLE >>> test=*# copy foo from stdin; >>> Enter data to be copied followed by a newline. >>> End with a backslash and a period on a line by itself. >>> >>>>> 1 a >>>>> 2 b >>>>> 2 c >>>>> \. >>>>> >>> ERROR: duplicate key violates unique constraint "foo_pkey" >>> CONTEXT: COPY foo, line 3: "2 c" >>> test=*# >>> >>> >>> I can type the wrong key and the error occurs later with the finaly \. >>> >>> >>> Andreas >>> -- >>> Andreas Kretschmer >>> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) >>> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 1: if posting/reading through Usenet, please send an appropriate >>> subscribe-nomail command to majordomo@postgresql.org so that >>> your >>> message can get through to the mailing list cleanly >>> >>> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: Don't 'kill -9' the postmaster >> > >
pgsql-general by date: