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

[Kevin Murphy]

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 ma*******@postgresql.org

[Bruce Momjian]

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 ma*******@postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Kevin Murphy]

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|TCTCCTTATCCACTTGTGTGTCTA G|0|0|||||gdb:
169029|GDB
#### END junk.dat ####
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Kevin Murphy <mu****@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Kevin Murphy]

On Sep 27, 2004, at 1:53 PM, Tom Lane wrote:

Kevin Murphy <mu****@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html