only seeing first of many COPY commands in input file

[Rachel McConnell]

Hi,

I am trying to import data using COPY, from a file containing thirty or
so COPY commands each with 0 or more rows of data. Reason, I have a
small data set I want to include into a database with an identical
schema, with existing data. I figured a good way to do this would be to
use pg_dump on the small dataset, trim out the table definitions and
constraints, and run the resulting file. (I also reordered the COPY
statements to allow for FK constraints.)

$ psql -U username -f filename rachel_philly

The result I'm getting is that the first COPY statement is executed, and
none of the rest of them. No error messages appear. I've run through
several of the rest of the statements by commenting out the statement
that ran and running the file again, and in each case the data from the
first COPY statement is added to the database, but no data from any of
the rest of the COPY statements is imported.

When I restore normally from an untrimmed pg_dump file, all the COPY
statements are run without difficulty. I have read over the beginning
and end of the file but don't see anything that looks like a START ..
COMMIT block, or any similar thing that would flag for "run all of these
commands" vs. "only run the first command and then stop".

rachel=> select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3
(Debian 20040401)
(1 row)

rachel_philly=> \q

rachel@qbert /cygdrive/d/test
$ psql --version
psql (PostgreSQL) 7.4.2
contains support for command-line editing
I wouldn't think the version mismatch is causing this, since the full
restore works fine...

Any ideas on what I may be doing wrong, or further avenues of research
would be much appreciated!

Rachel
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Terry Lee Tucker]

We are loading a whole database using multiple copy command from inside a sql
script. Here is a couple of sample lines:

COPY choice FROM '/esc/pgrnd/prog/choice/choice.d.txt' DELIMITERS '|' WITH
NULL AS '?'; \i choice/choicePostLoad. sql;

COPY glacct FROM '/esc/pgrnd/prog/glacct/glacct.d.txt' DELIMITERS '|' WITH
NULL AS '?'; \i glacct/glacctPostLoad. sql;

We are also applying indicies and table/column comments to the tables after
the COPY command in the PostLoad files listed above.

I execute the command to load all this as:

psql rnd < load_data.sql

Do you have semi-colons at the end of each of the commands? By the way, I'm
running the same version as you.

On Thursday 18 November 2004 04:11 pm, Rachel McConnell saith:

Hi,

I am trying to import data using COPY, from a file containing thirty or
so COPY commands each with 0 or more rows of data. Reason, I have a
small data set I want to include into a database with an identical
schema, with existing data. I figured a good way to do this would be to
use pg_dump on the small dataset, trim out the table definitions and
constraints, and run the resulting file. (I also reordered the COPY
statements to allow for FK constraints.)

$ psql -U username -f filename rachel_philly

The result I'm getting is that the first COPY statement is executed, and
none of the rest of them. No error messages appear. I've run through
several of the rest of the statements by commenting out the statement
that ran and running the file again, and in each case the data from the
first COPY statement is added to the database, but no data from any of
the rest of the COPY statements is imported.

When I restore normally from an untrimmed pg_dump file, all the COPY
statements are run without difficulty. I have read over the beginning
and end of the file but don't see anything that looks like a START ..
COMMIT block, or any similar thing that would flag for "run all of these
commands" vs. "only run the first command and then stop".

rachel=> select version();
version
---------------------------------------------------------------------------
--------------- PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc
(GCC) 3.3.3 (Debian 20040401)
(1 row)

rachel_philly=> \q

rachel@qbert /cygdrive/d/test
$ psql --version
psql (PostgreSQL) 7.4.2
contains support for command-line editing
I wouldn't think the version mismatch is causing this, since the full
restore works fine...

Any ideas on what I may be doing wrong, or further avenues of research
would be much appreciated!

Rachel
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Quote: 67
"Nothing is more despicable than a professional talker who uses his
words as a quack uses his remedies."

--Francois de Salignac de la Mothe Fenelon

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org