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: terry@esc1.com
