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

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