Re: help with COPY from .csv file into database

On Wed, 2004-08-04 at 16:29, michael@floog.net wrote:
> I have tried again to copy using a suggestion sent to me from another
> new user on the list:
>
> Attempt 1 --
>
> TEST_DB=# copy matters from '/home/floog/TEST_DB.csv' delimiter ',';
> ERROR:  could not open file "/home/floog/TEST_DB.csv" for reading:
> Permission denied
>
> Okay, so I chmod the file to be read/write by anyone -- (chmod 0777
> /home/floog/TEST_DB.csv)
>
> TEST_DB=# copy matters from '/home/floog/TEST_DB.csv' delimiter ',';
> ERROR:  could not open file "/home/floog/TEST_DB.csv" for reading:
> Permission denied

You also have to make the whole path accessible; presumably the
directory /home/floog is not accessible to the postgres user.  (The
backend runs with the permission of the user who starts the postmaster,
which is commonly postgres.)

> Still no go so I copied the file into my PGDATA directory -
> /usr/local/postgres
>
> TEST_DB=# copy matters from '/usr/local/postgres/TEST_DB.csv' delimiter
> ',';
> ERROR:  invalid input syntax for integer: ""CFW""
> CONTEXT:  COPY matters, line 1, column matter_num: ""CFW""
> TEST_DB=# select * from matters;
...
> (0 rows)
>
> I understand the first error about the "CFW" being in a field designated
> in an integer format.  That's okay.  CFW are the initials of a person
> at my office and that's just a data entry error -- wrong data in the
> wrong column.
>
> But I don't understand why none of the data from the .csv file was
> copied to the database.  SELECT * FROM matters - results in nothing but
> a line of hyphens.

COPY happens in a transaction; either all of the data goes in or none of
it does.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "And not only so, but we glory in tribulations also;
      knowing that tribulation worketh patience; And
      patience, experience; and experience, hope."
                                        Romans 5:3,4