Hello, Alexander,
On Mon, Jul 23, 2018 at 05:21:43PM +0300, Alexander Korotkov wrote:
> Thank you, Arthur. These examples shows downside of this patch, where
> users may be faced with incompatibility. But it's good that this
> situation can be handled by altering format string. I think these
> examples should be added to the documentation and highlighted in
> release notes.
I updated the documentation. I added a tip text which explains
how to_timestamp() and to_date() handled ordinary text prior to
PostgreSQL 11 and how they should handle ordinary text now.
There is now changes in the code and tests.
--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index edc9be92a6..db1da7bee7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6264,7 +6264,8 @@ SELECT regexp_match('abc01234xyz',
'(?:(.*?)(\d+)(.*)){1,1}');
<function>to_timestamp</function> and <function>to_date</function>
skip multiple blank spaces in the input string unless the
<literal>FX</literal> option is used. For example,
- <literal>to_timestamp('2000 JUN', 'YYYY
MON')</literal> works, but
+ <literal>to_timestamp('2000 JUN', 'YYYY
MON')</literal> and
+ <literal>to_timestamp('2000 JUN',
'YYYY MON')</literal> work, but
<literal>to_timestamp('2000 JUN', 'FXYYYY
MON')</literal> returns an error
because <function>to_timestamp</function> expects one space only.
<literal>FX</literal> must be specified as the first item in
@@ -6272,6 +6273,19 @@ SELECT regexp_match('abc01234xyz',
'(?:(.*?)(\d+)(.*)){1,1}');
</para>
</listitem>
+ <listitem>
+ <para>
+ <function>to_timestamp</function> and <function>to_date</function> don't
+ skip multiple non letter and non digit characters in the input string,
+ but skip them in the formatting string. For example,
+ <literal>to_timestamp('2000-JUN', 'YYYY/MON')</literal> and
+ <literal>to_timestamp('2000/JUN', 'YYYY//MON')</literal> work, but
+ <literal>to_timestamp('2000//JUN', 'YYYY/MON')</literal>
+ returns an error because count of the "/" character in the input string
+ doesn't match count of it in the formatting string.
+ </para>
+ </listitem>
+
<listitem>
<para>
Ordinary text is allowed in <function>to_char</function>
@@ -6287,6 +6301,20 @@ SELECT regexp_match('abc01234xyz',
'(?:(.*?)(\d+)(.*)){1,1}');
string; for example <literal>"XX"</literal> skips two input characters
(whether or not they are <literal>XX</literal>).
</para>
+ <tip>
+ <para>
+ Prior to <productname>PostgreSQL</productname> 11 it was possible to
+ skip input ordinary text using non letter and non digit characters.
+ For example,
+ <literal>to_timestamp('2000y6m1d', 'yyyy-MM-DD')</literal> worked
+ before. But currently to skip ordinary text in the input string it
+ is necessary to use only letter characters. For example,
+ <literal>to_timestamp('2000y6m1d', 'yyyytMMtDDt')</literal> or
+ <literal>to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"')</literal>
will
+ skip <literal>y</literal>, <literal>m</literal> and
+ <literal>d</literal> now.
+ </para>
+ </tip>
</listitem>
<listitem>
diff --git a/src/backend/utils/adt/formatting.c
b/src/backend/utils/adt/formatting.c
index 30696e3575..e7547091cd 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -165,6 +165,8 @@ typedef struct
#define NODE_TYPE_END 1
#define NODE_TYPE_ACTION 2
#define NODE_TYPE_CHAR 3
+#define NODE_TYPE_SEPARATOR 4
+#define NODE_TYPE_SPACE 5
#define SUFFTYPE_PREFIX 1
#define SUFFTYPE_POSTFIX 2
@@ -955,6 +957,7 @@ typedef struct NUMProc
static const KeyWord *index_seq_search(const char *str, const KeyWord *kw,
const int *index);
static const KeySuffix *suff_search(const char *str, const KeySuffix *suf, int
type);
+static bool is_separator_char(const char *str);
static void NUMDesc_prepare(NUMDesc *num, FormatNode *n);
static void parse_format(FormatNode *node, const char *str, const KeyWord *kw,
const KeySuffix *suf, const int *index, int ver,
NUMDesc *Num);
@@ -1044,6 +1047,16 @@ suff_search(const char *str, const KeySuffix *suf, int
type)
return NULL;
}
+static bool
+is_separator_char(const char *str)
+{
+ /* ASCII printable character, but not letter or digit */
+ return (*str > 0x20 && *str < 0x7F &&
+ !(*str >= 'A' && *str <= 'Z') &&
+ !(*str >= 'a' && *str <= 'z') &&
+ !(*str >= '0' && *str <= '9'));
+}
+
/* ----------
* Prepare NUMDesc (number description struct) via FormatNode struct
* ----------
@@ -1319,7 +1332,14 @@ parse_format(FormatNode *node, const char *str, const
KeyWord *kw,
if (*str == '\\' && *(str + 1) == '"')
str++;
chlen = pg_mblen(str);
- n->type = NODE_TYPE_CHAR;
+
+ if (ver == DCH_TYPE && is_separator_char(str))
+ n->type = NODE_TYPE_SEPARATOR;
+ else if (isspace((unsigned char) *str))
+ n->type = NODE_TYPE_SPACE;
+ else
+ n->type = NODE_TYPE_CHAR;
+
memcpy(n->character, str, chlen);
n->character[chlen] = '\0';
n->key = NULL;
@@ -2990,12 +3010,73 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar
*out)
for (n = node, s = in; n->type != NODE_TYPE_END && *s != '\0'; n++)
{
- if (n->type != NODE_TYPE_ACTION)
+ if (n->type == NODE_TYPE_SPACE)
+ {
+ /*
+ * In non FX (fixed format) mode we don't insist that
the consumed
+ * character matches the format's character.
+ */
+ if (!fx_mode)
+ {
+ if (isspace((unsigned char) *s) ||
is_separator_char(s))
+ s++;
+
+ continue;
+ }
+ /*
+ * In FX mode we insist that whitespace from the format
string
+ * matches whitespace from the input string.
+ */
+ else if (!isspace((unsigned char) *s))
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("unexpected character
\"%.*s\", expected space character \"%s\"",
+ pg_mblen(s), s,
n->character),
+ errhint("In FX mode,
punctuation in the input string "
+ "must exactly
match the format string.")));
+ }
+ else if (n->type == NODE_TYPE_SEPARATOR)
+ {
+ if (!fx_mode)
+ {
+ /*
+ * Ignore spaces before separator character
when not in FX
+ * mode.
+ */
+ while (*s != '\0' && isspace((unsigned char)
*s))
+ s++;
+
+ /*
+ * In non FX mode we don't insist that the
consumed character
+ * matches the format's character.
+ */
+ if (is_separator_char(s))
+ s++;
+
+ continue;
+ }
+ /*
+ * In FX mode we insist that separator character from
the format
+ * string matches separator character from the input
string.
+ */
+ else if (*n->character != *s)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("unexpected character
\"%.*s\", expected separator character \"%s\"",
+ pg_mblen(s), s,
n->character),
+ errhint("In FX mode,
punctuation in the input string "
+ "must exactly
match the format string.")));
+
+ s++;
+ continue;
+ }
+ else if (n->type != NODE_TYPE_ACTION)
{
/*
- * Separator, so consume one character from input
string. Notice
- * we don't insist that the consumed character match
the format's
- * character.
+ * Text character, so consume one character from input
string.
+ * Notice we don't insist that the consumed character
match the
+ * format's character.
+ * Text field ignores FX mode.
*/
s += pg_mblen(s);
continue;
diff --git a/src/test/regress/expected/horology.out
b/src/test/regress/expected/horology.out
index 63e39198e6..fb595c7b17 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -2769,14 +2769,32 @@ SELECT to_timestamp('97/2/16 8:14:30',
'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
Sat Feb 16 08:14:30 0097 PST
(1 row)
+SELECT to_timestamp('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS');
+ to_timestamp
+------------------------------
+ Fri Mar 18 23:38:15 2011 PDT
+(1 row)
+
SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD');
to_timestamp
------------------------------
Sat Jan 12 00:00:00 1985 PST
(1 row)
+SELECT to_timestamp('1985 FMMonth 12', 'YYYY "FMMonth" DD');
+ to_timestamp
+------------------------------
+ Sat Jan 12 00:00:00 1985 PST
+(1 row)
+
+SELECT to_timestamp('1985 \ 12', 'YYYY \\ DD');
+ to_timestamp
+------------------------------
+ Sat Jan 12 00:00:00 1985 PST
+(1 row)
+
SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
- '"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
+ '"My birthday-> Year:" YYYY, "Month:" FMMonth, "Day:" DD');
to_timestamp
------------------------------
Sun May 16 00:00:00 1976 PDT
@@ -2789,7 +2807,7 @@ SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
(1 row)
SELECT to_timestamp('15 "text between quote marks" 98 54 45',
- E'HH24 "\\text between quote marks\\"" YY MI SS');
+ E'HH24 "\\"text between quote marks\\"" YY MI SS');
to_timestamp
------------------------------
Thu Jan 01 15:54:45 1998 PST
@@ -2810,6 +2828,21 @@ SELECT to_timestamp('2000January09Sunday',
'YYYYFMMonthDDFMDay');
SELECT to_timestamp('97/Feb/16', 'YYMonDD');
ERROR: invalid value "/Fe" for "Mon"
DETAIL: The given value did not match any of the allowed values for this
field.
+SELECT to_timestamp('97/Feb/16', 'YY:Mon:DD');
+ to_timestamp
+------------------------------
+ Sun Feb 16 00:00:00 1997 PST
+(1 row)
+
+SELECT to_timestamp('97/Feb/16', 'FXYY:Mon:DD');
+ERROR: unexpected character "/", expected separator character ":"
+HINT: In FX mode, punctuation in the input string must exactly match the
format string.
+SELECT to_timestamp('97/Feb/16', 'FXYY/Mon/DD');
+ to_timestamp
+------------------------------
+ Sun Feb 16 00:00:00 1997 PST
+(1 row)
+
SELECT to_timestamp('19971116', 'YYYYMMDD');
to_timestamp
------------------------------
@@ -2966,7 +2999,7 @@ SELECT to_timestamp('2011-12-18 11:38 20',
'YYYY-MM-DD HH12:MI TZM');
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
to_timestamp
------------------------------
- Sun Dec 18 03:38:15 2011 PST
+ Sun Dec 18 23:38:15 2011 PST
(1 row)
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
@@ -2996,7 +3029,31 @@ SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD
HH24:MI:SS');
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
to_timestamp
------------------------------
- Sun Dec 18 03:38:15 2011 PST
+ Sun Dec 18 23:38:15 2011 PST
+(1 row)
+
+SELECT to_timestamp('2000+ JUN', 'YYYY/MON');
+ to_timestamp
+------------------------------
+ Thu Jun 01 00:00:00 2000 PDT
+(1 row)
+
+SELECT to_timestamp('2000 +JUN', 'YYYY/MON');
+ to_timestamp
+------------------------------
+ Thu Jun 01 00:00:00 2000 PDT
+(1 row)
+
+SELECT to_timestamp('2000 +JUN', 'YYYY//MON');
+ to_timestamp
+------------------------------
+ Thu Jun 01 00:00:00 2000 PDT
+(1 row)
+
+SELECT to_timestamp('2000 +JUN', 'YYYY//MON');
+ to_timestamp
+------------------------------
+ Thu Jun 01 00:00:00 2000 PDT
(1 row)
SELECT to_date('2011 12 18', 'YYYY MM DD');
@@ -3014,13 +3071,13 @@ SELECT to_date('2011 12 18', 'YYYY MM DD');
SELECT to_date('2011 12 18', 'YYYY MM DD');
to_date
------------
- 12-08-2011
+ 12-18-2011
(1 row)
SELECT to_date('2011 12 18', 'YYYY MM DD');
to_date
------------
- 02-18-2011
+ 12-18-2011
(1 row)
SELECT to_date('2011 12 18', 'YYYY MM DD');
diff --git a/src/test/regress/sql/horology.sql
b/src/test/regress/sql/horology.sql
index ebb196a1cf..e9accdf30c 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -392,15 +392,21 @@ SELECT to_timestamp('0097/Feb/16 --> 08:14:30',
'YYYY/Mon/DD --> HH:MI:SS');
SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
+SELECT to_timestamp('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS');
+
SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD');
+SELECT to_timestamp('1985 FMMonth 12', 'YYYY "FMMonth" DD');
+
+SELECT to_timestamp('1985 \ 12', 'YYYY \\ DD');
+
SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
- '"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
+ '"My birthday-> Year:" YYYY, "Month:" FMMonth, "Day:" DD');
SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
SELECT to_timestamp('15 "text between quote marks" 98 54 45',
- E'HH24 "\\text between quote marks\\"" YY MI SS');
+ E'HH24 "\\"text between quote marks\\"" YY MI SS');
SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
@@ -408,6 +414,12 @@ SELECT to_timestamp('2000January09Sunday',
'YYYYFMMonthDDFMDay');
SELECT to_timestamp('97/Feb/16', 'YYMonDD');
+SELECT to_timestamp('97/Feb/16', 'YY:Mon:DD');
+
+SELECT to_timestamp('97/Feb/16', 'FXYY:Mon:DD');
+
+SELECT to_timestamp('97/Feb/16', 'FXYY/Mon/DD');
+
SELECT to_timestamp('19971116', 'YYYYMMDD');
SELECT to_timestamp('20000-1116', 'YYYY-MMDD');
@@ -464,6 +476,11 @@ SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD
HH24:MI:SS');
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
+SELECT to_timestamp('2000+ JUN', 'YYYY/MON');
+SELECT to_timestamp('2000 +JUN', 'YYYY/MON');
+SELECT to_timestamp('2000 +JUN', 'YYYY//MON');
+SELECT to_timestamp('2000 +JUN', 'YYYY//MON');
+
SELECT to_date('2011 12 18', 'YYYY MM DD');
SELECT to_date('2011 12 18', 'YYYY MM DD');
SELECT to_date('2011 12 18', 'YYYY MM DD');
