Re: Problems importing csv files

Thanks to Tim, Robert, et. al. for helping with the import csv problem. Using your suggestions, I believe I was able to rectify that particular issue.

However, now I am having the problem of using the proper DATE and TIME syntax. I have looked in the Postgres manual, and it gives a whole long list of different date and time functions, but I am not sure which one I should use and how to integrate it into a "create table as" command.

the csv file was created in MS Excel (yes I use Windows).

At the moment, the easy work-around is to simply define each column as "text". The csv imported perfectly doing it this way, however, I want to use the proper syntax and formatting whenever possible:

convert date as text (mm/dd/yyyy) to yyyy-mm-dd

convert time as text (hh:mm) to time without timezone hh:mm:ss

So, my question is, what do I need to do to my query language to ensure that posgresql processes the query successfully using the specified formats? I'm using v. 8.3 of PostgreSQL.

thanks in advance,

Kindra

________________________________
From: Tim Ryan <twilight28(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Kindra Martinenko <kindramart(at)yahoo(dot)com>
Sent: Thursday, June 25, 2009 6:53:03 AM
Subject: Re: [NOVICE] Problems importing csv files

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