Re: The COPY command and csv files

Thank you to everyone who replied. After removing double quotes from
all NULL values and switching to my superuser, I could copy just fine.
Apparently my error was my use of stdin as a regular user. I knew I
couldn't use

COPY pwt61_test FROM '/home/emf/pwt61_test' # (that is, FROM 'file')

as a regular user and from what I read I thought stdin was to be used
when you are not a superuser, as in:

COPY pwt61_test FROM stdin
WITH DELIMITER ','
CSV QUOTE AS '"'
NULL AS 'na';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> /home/emf/pwt61_test.csv
>> \.
ERROR: missing data for column "isocode"
CONTEXT: COPY pwt61_oecd, line 1: "/home/emf/pwt61_test.csv"

It wasn't until I saw Tom Lane's post that I realized that I was using
stdin wrong. Now its obvious why I was getting the error I was
getting. It was expecting to see data, not a file.

Apparently, you can't use the COPY command to copy a whole file
without being a super user? I didn't understand this from the errors
I received. I apologize to everyone. I didn't mean to waste your
time.

-Eric

On 4/18/06, Florian Reiser <florian(dot)reiser(at)ra-bc(dot)de> wrote:
> Hello Matthew,
>
> I've noticed that the numeric fields are quoted with ".
> Try it after you removed the " from the numeric fields.
>
> Is it now working?
>
> With kind regards
>
> Florian Reiser
>
> --
> http://www.ra-bc.de
> RA Unternehmensberatung
> Führen durch präzise Daten
>
> ""E. Matthew Finnin"" <emf(dot)storage(at)gmail(dot)com> schrieb im Newsbeitrag
> news:c939097c0604180006r5bead672t199985c7b8858f71(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
> > Currently I have removed all double quotations from my null values
> > ("na" has been changed to na). I have also removed the header line of
> > the csv file I am trying to copy into the table. I have tried various
> > forms of null values and everything else suggested to me, with no
> > luck.
> >
> > Here are the table definitions. I was trying to make it as simple as
> > possible:
> > emf=> \d pwt61_oecd
> > Table "public.pwt61_oecd"
> > Column | Type | Modifiers
> > ---------+---------+-----------
> > country | text |
> > isocode | text |
> > year | numeric |
> > pop | numeric |
> > xrat | numeric |
> > ppp | numeric |
> > cgdp | numeric |
> > cc | numeric |
> > ci | numeric |
> > cg | numeric |
> > p | numeric |
> > pc | numeric |
> > pg | numeric |
> > pi | numeric |
> > openc | numeric |
> > cgnp | numeric |
> > csave | numeric |
> > y | numeric |
> > rgdpl | numeric |
> > rgdpch | numeric |
> > rgdpeqa | numeric |
> > rgdpwok | numeric |
> > rgdptt | numeric |
> > openk | numeric |
> > kc | numeric |
> > kg | numeric |
> > ki | numeric |
> > grgdpch | numeric |
> >
> > This is the error I get after using the copy command:
> > ERROR: missing data for column "isocode"
> > CONTEXT: COPY pwt61_oecd, line 1:
> >
> > This is the first line of the csv file, where the error is occuring:
> > "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
> >
> > If I drop the column 'isocode' from the table and from the csv file, I
> > receive the following error:
> > ERROR: missing data for column "year"
> > CONTEXT: COPY pwt61_test, line 1:
> >
> > Here the first line of the csv file is:
> > "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
> >
> > Notice the error has remained in the second column and it doesn't
> > matter what value it holds or what the data type is.
> >
> > I am at a complete loss. Thank you again for your help.
> >
> > -Eric
> >
> >
> >
> >
> > On 4/14/06, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> >> E. Matthew Finnin wrote:
> >> > Thank you both for replying. When I wrote ' " ' I meant '"', I was
> >> > trying to make it easier to read for an email, but all I did was
> >> > confuse things. Anyway, I tried dropping the quote and delimiter
> >> > comments from the command, but I still received a missing data error
> >> > on line 1 of the csv file. The data I'm using is all OECD countries
> >> > selected from the Penn World Tables 6.1 website
> >> > (http://pwt.econ.upenn.edu/php_site/pwt61_form.php) and cutting and
> >> > pasting the output into a csv file.
> >> >
> >> > Bruce you mentioned there is a cvs backpatch for version 8.1.x. Maybe
> >> > this is my problem. How do I check if this is installed and, assuming
> >> > it isn't, how do I go about installing it? I've come across scripts
> >> > in the mail lists related to something like this, but I wasn't sure if
> >> > they were final or even for anyone other than developers. If its of
> >> > any interest to anyone, I installed Postgresql 8.1.x as an Kubuntu
> >> > package.
> >>
> >> OK, got it. If you remove the first line, does it work? Also, those
> >> "n/a" are not going to work because we don't allow the NULL indicator to
> >> be in double quotes. Try change "n/a" to n/a and see if that helps.
> >> Also try removing the first line to see if that helps. Also, show use
> >> the table definition you are trying to load into, and the error message,
> >> and a line generating an error.
> >>
> >> I don't think you need anything backpatched.
> >>
> >> --
> >> Bruce Momjian http://candle.pha.pa.us
> >> EnterpriseDB http://www.enterprisedb.com
> >>
> >> + If your life is a hard drive, Christ can be your backup. +
> >>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>