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