Обсуждение: COPY problem on -- strings

Hi,

I have "PostgreSQL 8.3.5, compiled by Visual C++ build 1400" on Windows OS.

I try to use the COPY command to optimize the backup/restore performance,
but I found a problem. I reproduce it below.

I create a database with a simple table:
  CREATE TABLE "A"
  (
    "Col1" integer NOT NULL,
    "Col2" character varying NOT NULL,
    CONSTRAINT "A_pkey" PRIMARY KEY ("Col1")
  )
  WITH (OIDS=FALSE);
  ALTER TABLE "A" OWNER TO postgres;

I populate the table with the statement:
  INSERT INTO "A" ( "Col1", "Col2" )
  VALUES (2, '-- any text' );

I backup the database plain with the command:
 pg_dump.exe -U postgres -F p -v -f "backup_plain.sql" "DemoDB"

I create a new database, and I run the script. But it rise me the error:
  ERROR:  syntax error at or near "1"
  LINE 49: 1 -- any text

I look for the error line and I saw how pg_dump created the script
statement:
  COPY "A" ("Col1", "Col2") FROM stdin;
  1 -- any text
  \.

Please let me know if this is a bug on pg_dump, or on the COPY syntax
design, or am I wrong ?
Do you know an workaround to make my backup/restore process available by
COPY, not by INSERT commands ?

TIA,
Sabin

"Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
> I backup the database plain with the command:
>  pg_dump.exe -U postgres -F p -v -f "backup_plain.sql" "DemoDB"

> I create a new database, and I run the script. But it rise me the error:
>   ERROR:  syntax error at or near "1"
>   LINE 49: 1 -- any text

> I look for the error line and I saw how pg_dump created the script
> statement:
>   COPY "A" ("Col1", "Col2") FROM stdin;
>   1 -- any text
>   \.

Is that the *first* error message you got?

My guess is that something went wrong with the COPY command, so that
psql failed to switch into copy-data mode and is trying to interpret
the following stuff as SQL commands.

            regards, tom lane

> Is that the *first* error message you got?
>

Yes it is.

In fact I made a mistake in the first email, so instead:
        INSERT INTO "A" ( "Col1", "Col2" )
        VALUES (2, '-- any text' );

please change with:
        INSERT INTO "A" ( "Col1", "Col2" )
        VALUES (1, '-- any text' );

However I suppose this doesn't change the problem :(.

Regards,
Sabin

Sabin Coanda wrote:
> Hi,
>
> I have "PostgreSQL 8.3.5, compiled by Visual C++ build 1400" on Windows OS.
>
> I try to use the COPY command to optimize the backup/restore performance,
> but I found a problem. I reproduce it below.

I can't reproduce it here on 8.3 on linux.

> I backup the database plain with the command:
>  pg_dump.exe -U postgres -F p -v -f "backup_plain.sql" "DemoDB"
>
> I create a new database, and I run the script. But it rise me the error:
>   ERROR:  syntax error at or near "1"
>   LINE 49: 1 -- any text
>
> I look for the error line and I saw how pg_dump created the script
> statement:
>   COPY "A" ("Col1", "Col2") FROM stdin;
>   1 -- any text
>   \.

That's what I see too, and it's fine here.

Try trimming the file down to just those lines, manually create the
database and table and see if you can run the copy then. If so, then
Tom's right and there's an error before the COPY. If not, then you've
got something odd in the file (bad line-ending, invalid high-bit
character or some such).

--
  Richard Huxton
  Archonet Ltd

Sorry, my fault that I run the script in the query window of pgAdmin, not in
the system console. I check it again in the system console and it works
well.

Thanks,
Sabin