On 4 March 2010 04:08, Tom Lane <t...@sss.pgh.pa.us> wrote: > Brendan Jurd <dire...@gmail.com> writes: >> For example, you're trying to import a date that is written as "Wed >> 3rd March, Q1 2010". You might give to_date a format string like 'Dy >> FMDDTH Month, "Q"Q YYYY' and expect to get the correct answer. If we >> start throwing an error on the Q field, then users would have to >> resort to some strange circumlocution to get around it. > > Hmm. That's an interesting test case: if Q throws an error, there > doesn't seem to be any way to do it at all, because there is no format > spec for ignoring non-constant text.
Not entirely true. It's possible, it's just not at all obvious: =# select to_date('Wed 3rd March, Q1 2010', 'Dy FMDDTH Month, "QQ" YYYY'); to_date ------------ 2010-03-03 (1 row) Anything in a format string which is quoted is ignored. Or to put it another way, putting stuff in quotes is telling to_date() that the characters in those positions are not important to you and should not be used to help construct the date result. It doesn't actually check that the characters in the source string match what you have put inside the quotes, it just skips over the quoted number of characters. I doubt anyone unfamiliar with the source code of the function would ever devise the above solution, and it's an ugly hack reliant on a quirk anyway. So a user in-the-field would probably just resort to running a regexp_replace() over the text in order to strip out the quarter before passing it to to_date(). > So at the moment my vote is "leave it alone". If we want to throw > error for Q then we should provide a substitute method of ignoring > a field. But we could just document Q as ignoring an integer for > input. Sounds good to me. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers