Skip site navigation (1) Skip section navigation (2)

Re: Problems importing csv files

From: Tim Ryan <twilight28(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Kindra Martinenko <kindramart(at)yahoo(dot)com>
Subject: Re: Problems importing csv files
Date: 2009-06-25 13:53:03
Message-ID: 9C0D6F7D-E095-4DDF-B26F-840A88C36788@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice

On Jun 22, 2009, at 19:29, Robert Schnabel <schnabelr(at)missouri(dot)edu>  
wrote:

>
> Kindra Martinenko wrote:
>>
>>
>> I tried:
>>
>> COPY BaxterCommercial
>> FROM E'H:\\transpor\\Traffic Counts\\TMS\\MarApr09\ 
>> \BaxterCommercial.csv'
>> WITH CSV;
>>
>> And it returned with this error:
>>
>>
>> ERROR:  relation "baxtercommercial" does not exist
>>
>> Any ideas?
>

I do a lot of csv importing and exporting on Windows. On my setup I  
find it much easier to use forward slashes in the file path. I  
struggled with escaping the normal Windows backslashes but never got  
it to work.

As for the error message, when you use the COPY query construct, the  
postgres service is trying to access the file as the postgres user  
that was created during install (assuming you didn't change the  
defaults), not the local or network Windows user you are logged in as.  
This can lead to some subtle permission denied errors, and as you can  
see, the error isn't properly descriptive in this case.

A few ways to work around this: 1) use the psql client's \copy command  
instead, which will run as the local Windows user as you would expect.  
2) change the permissions on the folder or file in question so that  
the postgres user has permission to access the file using the SQL COPY  
command.

There are a few other ways to work around the problem, but they go  
against best practices and create problems of their own.

Hope this helps.

Tim Ryan 
  

In response to

Responses

pgsql-novice by date

Next:From: SMITH, Phillip (external)Date: 2009-06-25 17:07:22
Subject: using the difference function
Previous:From: Tim RyanDate: 2009-06-25 13:36:44
Subject: Re: Problems importing csv files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group