Thread: using COPY table FROM STDIN within script run as psql -f file.sql

This is a tip for the record in case it helps somebody else in the
future.

I have an import script that relies on a stored procedure that runs as
a trigger on inserts into a temporary table.  The script looks like
this:

-- create table
-- ...
-- define procedure and trigger
-- ...
-- import data via COPY command:
COPY temp_table FROM STDIN WITH NULL AS '';

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!

The solution is to use the psql "\COPY" command instead (and remove the
trailing semi-colon, which cannot be used with psql commands).  I.e.
this command will work:

\COPY temp_table FROM STDIN WITH NULL AS '';

-Kevin Murphy

Would you provide a reproducable example?  Also, what PostgreSQL version
are you using?

---------------------------------------------------------------------------

Kevin Murphy wrote:
> This is a tip for the record in case it helps somebody else in the
> future.
>
> I have an import script that relies on a stored procedure that runs as
> a trigger on inserts into a temporary table.  The script looks like
> this:
>
> -- create table
> -- ...
> -- define procedure and trigger
> -- ...
> -- import data via COPY command:
> COPY temp_table FROM STDIN WITH NULL AS '';
>
> 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!
>
> The solution is to use the psql "\COPY" command instead (and remove the
> trailing semi-colon, which cannot be used with psql commands).  I.e.
> this command will work:
>
> \COPY temp_table FROM STDIN WITH NULL AS '';
>
> -Kevin Murphy
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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

Kevin Murphy <murphy@genome.chop.edu> writes:
> 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:

> COPY import_sts_tmp FROM STDIN WITH NULL AS '';

This command says to copy from the SQL script file.  You can use
psql's \copy command to get the effect you are after.

            regards, tom lane

On Sep 27, 2004, at 1:53 PM, Tom Lane wrote:
> Kevin Murphy <murphy@genome.chop.edu> writes:
>> psql -U egenome_test -P pager=off -f
>> /Users/murphy/cvs/egora/sql/data_build/junk.sql < ./junk.dat
>> COPY import_sts_tmp FROM STDIN WITH NULL AS '';
> This command says to copy from the SQL script file.  You can use
> psql's \copy command to get the effect you are after.

Yes, I discovered that \COPY worked.  Ah, so the COPY starts consuming
its own script -- on the line after the COPY command?  Maybe that is
why an error is given about the second column, then: the first column
consumes emptiness from the following blank line, leaving no data
source for the remaining columns of the first row?

-Kevin Murphy