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 majordomo@postg resql.org

[Bruce Momjian]

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



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

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

Kevin Murphy wrote:[color=blue]
> 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@postg resql.org
>[/color]

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.ph a.pa.us | (610) 359-1001
+ 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]

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

On Sep 25, 2004, at 9:06 PM, Bruce Momjian wrote:[color=blue][color=green]
>> 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![/color]
>
> Would you provide a reproducable example? Also, what PostgreSQL
> version
> are you using?[/color]


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_numbe rs 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 ####
CTTCGATCTCGTACG TAAGCCACAC|TCTC CTTATCCACTTGTGT GTCTAG|0|0||||| gdb:
169029|GDB
#### END junk.dat ####


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

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

Kevin Murphy <murphy@genome. chop.edu> writes:[color=blue]
> I can reproduce the problem with this command:[/color]
[color=blue]
> psql -U egenome_test -P pager=off -f
> /Users/murphy/cvs/egora/sql/data_build/junk.sql < ./junk.dat[/color]
[color=blue]
> with junk.sql and junk.dat as follows:[/color]
[color=blue]
> COPY import_sts_tmp FROM STDIN WITH NULL AS '';[/color]

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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Kevin Murphy]

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

On Sep 27, 2004, at 1:53 PM, Tom Lane wrote:[color=blue]
> Kevin Murphy <murphy@genome. chop.edu> writes:[color=green]
>> 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 '';[/color]
> This command says to copy from the SQL script file. You can use
> psql's \copy command to get the effect you are after.[/color]

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