Re: COPY command details

"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> writes:
> am Thu, dem 29.03.2007, um 0:13:09 -0700 mailte Benjamin Arai folgendes:
>> 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 \.

No, "during" is the right answer. The above only demonstrates that
libpq buffers COPY data in larger-than-one-line units --- once the
data gets to the backend it's inserted and checked a row at a time.

regards, tom lane