Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g5Az8-0003CH-SA for pgsql-hackers@arkaria.postgresql.org; Wed, 26 Sep 2018 14:40:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g5Az6-0002EG-QB for pgsql-hackers@arkaria.postgresql.org; Wed, 26 Sep 2018 14:40:12 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g5Az6-0002E7-Ap for pgsql-hackers@lists.postgresql.org; Wed, 26 Sep 2018 14:40:12 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g5Az2-0002kB-Fd for pgsql-hackers@postgresql.org; Wed, 26 Sep 2018 14:40:11 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id A724921C2BC7; Wed, 26 Sep 2018 17:40:05 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.27.108] (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 33EEA21C2BC6; Wed, 26 Sep 2018 17:40:05 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1537972805; bh=G+f09UTODuGbU+cn3Xl1tiLfZSohdRYzfECVTvI9BdY=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=XHr46l8eZmUpVZCJ3mU3LRQxvpcEx/bd7n39TTu0doSJ6XQoNQnrWc016HBwhl7YD jovOQKskPAHYN1ULJFqkyzX8HhFpjYIf6bTd+y2Azni5TIMxs+qz8UpHmJ2w9CMvOS 8tmaommGKa7oe/Cl/g+hI9iDYf3Et40pPqg4YHQc= Subject: Re: [HACKERS] Bug in to_timestamp(). To: Alexander Korotkov , amul sul Cc: prabhat.sahu@enterprisedb.com, "David G. Johnston" , Artur Zakirov , Tom Lane , Dmitry Dolgov <9erthalion6@gmail.com>, Robert Haas , Thomas Munro , Pavel Stehule , Alex Ignatov , Bruce Momjian , amul sul , pgsql-hackers References: From: Liudmila Mantrova Message-ID: Date: Wed, 26 Sep 2018 17:40:04 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------4B30BCEC5AE4D20299FB25B3" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------4B30BCEC5AE4D20299FB25B3 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 09/22/2018 10:00 PM, Alexander Korotkov wrote: > On Thu, Sep 20, 2018 at 3:52 PM Alexander Korotkov > wrote: >> On Thu, Sep 20, 2018 at 6:09 AM amul sul wrote: >>> Agreed, thanks for working on this. >> Pushed, thanks. > Please, find attached patch improving documentation about > letters/digits in to_date()/to_timestamp() template string. I think > it needs review from native English speaker. > > ------ > Alexander Korotkov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > Hi Alexander, I'm not a native speaker, but let me try to help. A new doc version is attached. -- Liudmila Mantrova Technical writer at Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------4B30BCEC5AE4D20299FB25B3 Content-Type: text/x-patch; name="to_timestamp_letters_digits-0001.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="to_timestamp_letters_digits-0001.patch" diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9a7f683..1532bcc 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -6286,13 +6286,46 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); exceeds the number of separators in the template. - If FX is specified, separator in template string - matches to exactly one character in input string. Notice we don't insist - input string character to be the same as template string separator. + If FX is specified, a separator in the template + string matches exactly one character in the input string. The + input string character does not need to be the same as the template + string separator. For example, to_timestamp('2000/JUN', 'FXYYYY MON') works, but to_timestamp('2000/JUN', 'FXYYYY  MON') - returns an error because a space second template string space consumed - letter J from the input string. + returns an error because the second space in the template string + consumes letter J of the input string. + + + + + + Template strings of to_timestamp and + to_date functions can also contain arbitrary + letters/digits between patterns. Such letters/digits can match + any characters in the input string. For example, + to_timestamp('2000yJUN', 'YYYYxMON') works. + + + Letters/digits consume an input string character only if the number + of extra characters at the beginning of the input string or between + the identified date/time values is less than or equal to the number + of the corresponding characters in the template string. This ensures + that the template string does not consume any characters of date/time + values when used without the FX option, even if + a letter/digit separator in the input string appears after a space. + For example, to_timestamp('2000y JUN', 'YYYYxMON') + works, but to_timestamp('2000 yJUN', 'YYYYxMON') + returns an error. + + + Note that if the template string contains an arbitrary letter, + the pattern that precedes this letter becomes greedy and tries + to match as many characters as possible. For example, + to_timestamp('2000906901', 'YYYYxMMxDD') + fails because the YYYY pattern matches + the whole input string instead of the first four characters. + Patterns separated by digits are non-greedy, so + to_timestamp('2000906901', 'YYYY0MM0DD') works fine. --------------4B30BCEC5AE4D20299FB25B3--