On Fri, Jun 26, 2009 at 2:38 PM, Kindra Martinenko
<kindramart@yahoo.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