Re: The COPY command and csv files - Mailing list pgsql-novice

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.reiser@ra-bc.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.storage@gmail.com> schrieb im Newsbeitrag
> news:c939097c0604180006r5bead672t199985c7b8858f71@mail.gmail.com...
> > 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@candle.pha.pa.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
>