By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,512 Members | 3,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,512 IT Pros & Developers. It's quick & easy.

COPY command with blank incoming values

P: n/a
Does COPY require values for each column even though the database field
is not set to NOT NULL? I did a COPY TO first to line up the data and
then COPY FROM a tab delimited file. Here are the first 5 fields in the
first line of the incoming file (omit the quotes, I put them there to
see where the start and end of the line is), you should find 3 tabs
after the second '1':

'1 1 '

I have a table with this structure below and when trying to COPY the tab
separated file into the table, it says it expects and Integer for the
field that does not require a value:

CREATE TABLE "public"."tblxrf" (
"xrf_id" SERIAL,
"seq_no" SMALLINT NOT NULL,
"xlno" SMALLINT,
"site" INTEGER,
"insp" DOUBLE PRECISION,
"flr" VARCHAR(10),
"side" VARCHAR(25),
"room" VARCHAR(25),
"source" VARCHAR(50),
"sub" VARCHAR(50),
"feat" VARCHAR(50),
"cnd" VARCHAR(25),
"clr" VARCHAR(25),
"note" VARCHAR(255),
"ssec" DOUBLE PRECISION,
"date/time" TIMESTAMP WITHOUT TIME ZONE,
"cycle" VARCHAR(25),
"di" DOUBLE PRECISION,
"result" CHAR(3),
"pbl" NUMERIC(3,2),
"pbl_error" NUMERIC(3,2),
"pbk " NUMERIC(3,2),
"pbk_error" NUMERIC(3,2),
"pbc " NUMERIC(3,2),
"pbc_error" NUMERIC(3,2),
"res " VARCHAR(25),
"res_error" VARCHAR(25),
CONSTRAINT "tblxrf_pkey" PRIMARY KEY("xrf_id")
) WITH OIDS;

ohc=# copy tblxrf from '/home/admin/xrf-all.tab';
ERROR: invalid input syntax for integer: ""
CONTEXT: COPY tblxrf, line 1, column xlno: ""

--
Robert
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Yes. I believe copy does the equivalent to INSERT INTO tblxrf
VALUES(xxx/txxx/t/xxx/t.....) so every column must be accounted for.
You could just add additional tabs right next to each other to represent
the rest of the columns.
On Thu, 2004-07-01 at 19:31, Robert Fitzpatrick wrote:
Does COPY require values for each column even though the database field
is not set to NOT NULL? I did a COPY TO first to line up the data and
then COPY FROM a tab delimited file. Here are the first 5 fields in the
first line of the incoming file (omit the quotes, I put them there to
see where the start and end of the line is), you should find 3 tabs
after the second '1':

'1 1 '

I have a table with this structure below and when trying to COPY the tab
separated file into the table, it says it expects and Integer for the
field that does not require a value:

CREATE TABLE "public"."tblxrf" (
"xrf_id" SERIAL,
"seq_no" SMALLINT NOT NULL,
"xlno" SMALLINT,
"site" INTEGER,
"insp" DOUBLE PRECISION,
"flr" VARCHAR(10),
"side" VARCHAR(25),
"room" VARCHAR(25),
"source" VARCHAR(50),
"sub" VARCHAR(50),
"feat" VARCHAR(50),
"cnd" VARCHAR(25),
"clr" VARCHAR(25),
"note" VARCHAR(255),
"ssec" DOUBLE PRECISION,
"date/time" TIMESTAMP WITHOUT TIME ZONE,
"cycle" VARCHAR(25),
"di" DOUBLE PRECISION,
"result" CHAR(3),
"pbl" NUMERIC(3,2),
"pbl_error" NUMERIC(3,2),
"pbk " NUMERIC(3,2),
"pbk_error" NUMERIC(3,2),
"pbc " NUMERIC(3,2),
"pbc_error" NUMERIC(3,2),
"res " VARCHAR(25),
"res_error" VARCHAR(25),
CONSTRAINT "tblxrf_pkey" PRIMARY KEY("xrf_id")
) WITH OIDS;

ohc=# copy tblxrf from '/home/admin/xrf-all.tab';
ERROR: invalid input syntax for integer: ""
CONTEXT: COPY tblxrf, line 1, column xlno: ""


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

Nov 23 '05 #2

P: n/a
Robert Fitzpatrick <ro****@webtent.com> writes:
Does COPY require values for each column even though the database field
is not set to NOT NULL?


If you want a NULL then you have to write the specified null
representation (which is \N by default, not an empty string).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.