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 ####