Re: Problems importing csv files

On Fri, Jun 26, 2009 at 2:38 PM, Kindra Martinenko <kindramart(at)yahoo(dot)com>wrote:

>
> 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
>

You should be able to use the copy command but you may need to change the
'DATESTYLE' configuration first. Usually this is set to ISO,MDY. If the
data in your csv file is in the format of mm/dd/yyyy this should copy
straight into a column that uses date as its type. The same should work fine
for the times.

You can check the value of datestyle before you run the copy command by
using 'SHOW DATESTYLE;' If it doesn't come back with 'ISO,MDY' you can set
it with 'SET datestyle = 'ISO,MDY' and then run your copy command again.

Guy Flaherty