Re: help with COPY from .csv file into database

On Wed, 2004-08-04 at 16:29, michael(at)floog(dot)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(at)lfix(dot)co(dot)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