[Babase] Re: Putting Foxpro files on the web
Karl O. Pinc
babase@www.eco.princeton.edu
Thu, 06 Oct 2005 06:24:33 +0000
On 10/05/2005 03:18:44 PM, Leah Gerber wrote:
> Karl,
>
> My next question with regards to copying tables has to do with dates.
> Do I need to change a date setting (within my new table in the
> sandbox) from MDY to DMY somewhere? I am getting an error saying that
> I have "invalid input syntax for type date". My foxpro file is DMY. I
> don't know how to check to see what the database is set to and change
> the setting if necessary. My dates are in the form 25/05/1999.
The DBs are set to
european (DMY) date format. (Try: SHOW DATESTYLE;)
Are you using:
\copy ... delimiter ',' ...
?
or are you telling it it's CSV?
OTOH, the server date format may not apply, as psql is a client
and may possibly be doing things to the date before giving it
to the server. Try:
export LC_TIME=en_GB
and then doing things. (That should set your Unix environment to
the Great Briton way of doing dates and times.)
Then again, the server may (possibly) want single quotes around
all data that's not a numeric column. In which case I can
fudge you a quick program to alter the text data files.
Here's some relevant docs regards server datestyles:
http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-DATESTYLE
http://www.postgresql.org/docs/8.0/static/sql-show.html
http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-CLIENT
(See 16.4.8.2. Locale and Formatting)
http://www.postgresql.org/docs/8.0/static/datatype-datetime.html
http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS-GENERIC
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein