Re: using COPY table FROM STDIN within script run as psql

On Sep 25, 2004, at 9:06 PM, Bruce Momjian wrote:
>> However, when run as "psql -f import.sql <data.file", it does not work
>> if you use the SQL "COPY" command, even if you are running psql on the
>> database server. You get an error like this: ERROR: missing data for
>> column "somecol". An interesting red-herring is that the column
>> mentioned is not necessarily the first column in the table!
>
> Would you provide a reproducable example? Also, what PostgreSQL
> version
> are you using?

I'm using 7.4.5 on Mac OS X.

I can reproduce the problem with this command:

psql -U egenome_test -P pager=off -f
/Users/murphy/cvs/egora/sql/data_build/junk.sql < ./junk.dat

with junk.sql and junk.dat as follows:

#### BEGIN junk.sql ####
DROP TABLE import_sts_tmp CASCADE;
CREATE TABLE import_sts_tmp (
primer1 text,
primer2 text,
product_length_left integer,
product_length_right integer,
chromosome text,
primary_name text,
d_name text,
accession_numbers text,
aliases text,
source varchar(20)
);

DROP FUNCTION import_sts_tmp_func() CASCADE;
CREATE FUNCTION import_sts_tmp_func() RETURNS trigger
AS '
DECLARE
BEGIN
RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER import_sts_tmp_trigger
AFTER INSERT
ON import_sts_tmp
FOR EACH ROW
EXECUTE PROCEDURE import_sts_tmp_func();

-- Now do the load into the temporary table.
-- The row trigger will update the elements and identifiers tables.
COPY import_sts_tmp FROM STDIN WITH NULL AS '';

DROP TABLE import_sts_tmp CASCADE;
#### END junk.sql ####

#### BEGIN junk.dat ####
#### Replace vertical bars with tabs to test ####
CTTCGATCTCGTACGTAAGCCACAC|TCTCCTTATCCACTTGTGTGTCTAG|0|0|||||gdb:
169029|GDB
#### END junk.dat ####