**I apologize in advance if this is a familiar question, but I couldn't
find an answer**
I am attempting to copy a csv file into a table, but no matter what I
can think of I get one of two errors. I am using Postgresql 8.1.2.
1.) Here is the first COPY command I gave:
COPY pwt61_oecd FROM stdin
WITH DELIMITER AS ','
CSV QUOTE AS ' " '
NULL AS 'na'
;
And here is the error:
ERROR: missing data for column "isocode"
CONTEXT: COPY pwt61_oecd, line 1 '/home/.../data/pwt61_oecd.csv"
The file I am attempting to copy is very large, but there are no empty
spaces that I can find, and the column "isocode" certainly has no
empty spaces or NULL values. Here are the first two lines of the
pwt61_oecd.csv file:
"country","isocode","year","POP","XRAT","PPP","cgdp","cc","ci","cg","p","pc","pg","pi","openc","cgnp","csave","y","rgdpl","rgdpch","rgdpeqa","rgdpwok","rgdptt","openk","kc","kg","ki","grgdpch"
"Australia","AUS","1950","8434.0650803","0.8931999704","0.515977685","1836.3303946","60.365553134","27.690222887","7.9941116957","57.767319935","53.853430186","73.898773273","61.642604011","47.850011457","na","31.64033517","95.021059091","9113.8072046","9173.8190347","10576.40502","22078.709149","9988.2503259","19.331539711","65.924922543","13.104642709","26.200404435","na"
(I attempted the same copy command after changing all "na" to "NULL"
and I also tried the same command after removing column headers (1st
line) from the csv file, but I received the same error message each
time.)
2.) Curious if I would get the same error, I attemtped to use the
\copy command in psql, but this time I removed the column headers from
the csv file:
\copy pwt61_oecd FROM '/home/.../data/pwt61_oecd2.csv' WITH DELIMITER
AS ',' CSV QUOTE AS ' " ' NULL AS 'na'
And the error report:
ERROR: invalid input syntax for type numeric: "na"
CONTEXT: COPY pwt61_oecd, line 1, column cgnp: "na"
Obviously I haven't used the COPY or \copy functions properly. Or I
am using the wrong data type. Could someone please point out my
mistake(s)? And on a unrelated note, in a seperate table I would like
to import there are exponential numbers (e.g. 3.77E-13). Do I need to
convert these to regular numbers if I wish to use the numeric data
type? Thank you.
-Eric
