Re: [GENERAL] Junk date getting uploaded into date field

2013-11-06 Thread John R Pierce
On 11/5/2013 10:29 AM, Steve Crawford wrote: The to_date and to_timestamp functions do minimal input error-checking and are intended for conversion of non-standard formats that cannot be handled by casting. These functions will attempt to convert illegal dates to the best of their ability, e.g.

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-06 Thread Albe Laurenz
Steve Crawford wrote: >> There is a comment in utils/adt/formatting.c: >> >> * This function does very little error checking, e.g. >> * to_timestamp('20096040','MMDD') works >> >> > I think the place for such warnings in addition to the source-code is in > the documentation. This or similar

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Steve Crawford
On 11/05/2013 05:29 AM, Albe Laurenz wrote: Thomas Kellerer wrote: bsreejithin, 05.11.2013 13:14: Not able to post the attached details as a comment in the reply box, so attaching it as an image file : It would have much easi

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Michael Nolan
On 11/5/13, bsreejithin wrote: > > I am not sure why : select to_date('33-OCT-2013', 'dd-mon-') > > is returning 2013-11-02. > > For cases like the issue I am facing, where we need to raise an error > saying > the data is wrong, DB manipulating the data is not proper. Try using a cast to date

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Tom Lane
bsreejithin writes: > I am not sure why : select to_date('33-OCT-2013', 'dd-mon-') > is returning 2013-11-02. > For cases like the issue I am facing, where we need to raise an error saying > the data is wrong, DB manipulating the data is not proper. Then don't use to_date(). Just entering t

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Tom Lane
Albe Laurenz writes: > There is a comment in utils/adt/formatting.c: > * This function does very little error checking, e.g. > * to_timestamp('20096040','MMDD') works > So at least this is not by accident. No, it isn't. This is in fact the traditional behavior of Unix time conversion uti

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Reid Thompson
On Tue, 2013-11-05 at 04:14 -0800, bsreejithin wrote: > Not able to post the attached details as a comment in the reply box, so > attaching it as an image file : > > > > > -- > View this message in context: > http://postgresql.

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Albe Laurenz
Thomas Kellerer wrote: > bsreejithin, 05.11.2013 13:14: >> Not able to post the attached details as a comment in the reply box, so >> attaching it as an image file : >> > > It would have much easier if you had simply used copy &

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Edson Richter
Em 05/11/2013 10:36, Thomas Kellerer escreveu: bsreejithin, 05.11.2013 13:14: Not able to post the attached details as a comment in the reply box, so attaching it as an image file : It would have much easier if you had simply

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread bsreejithin
I am not using any mail client.I was directly trying to post the content I attached.Got a connection reset by peer error on submit.Thought some issue with the browser - so tried with firefox, chrome as well, in addition to IE - got the same error there also. Any way, that's not the issue. I am not

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Thomas Kellerer
bsreejithin, 05.11.2013 13:14: > Not able to post the attached details as a comment in the reply box, so > attaching it as an image file : > It would have much easier if you had simply used copy & paste to post a text version

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread bsreejithin
Not able to post the attached details as a comment in the reply box, so attaching it as an image file : -- View this message in context: http://postgresql.1045698.n5.nabble.com/Junk-date-getting-uploaded-into-date-field-tp57

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread Thomas Kellerer
bsreejithin, 05.11.2013 10:04: > We have a csv file which we upload into postgres DB. If there are some > errors, like a data mismatch with the database table columns, postgres > should raise and error and upload should fail. > > What is happening now is that, in case we get some junk date in the

Re: [GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread John R Pierce
On 11/5/2013 1:04 AM, bsreejithin wrote: A wrong date like ,say, 33-Oct-2013 gets converted as 2-Nov-2013 when the data is loaded into a date field. No error raised.Data gets uploaded! um. postgresql won't do that conversion postgres=# select '33-Oct-2013'::date; ERROR: date/time field

[GENERAL] Junk date getting uploaded into date field

2013-11-05 Thread bsreejithin
We have a csv file which we upload into postgres DB. If there are some errors, like a data mismatch with the database table columns, postgres should raise and error and upload should fail. What is happening now is that, in case we get some junk date in the upload file, postgres does auto-correctio