From eae5184d11d67edf49c114e15abc0223df8f5432 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Wed, 8 Mar 2000 01:34:41 +0000 Subject: [PATCH] Hi, the to_char() source code is large, here are regression tests for numeric/timestamp/int8 part. It is probably enough test for formatting code in the formatting.c module. The others (float4/float8/int4) types share this formatting code and eventual bugs for these types aren't few probable. Patch fix timestamp_to_char() for infinity/invalid timestamp too. Karel --- src/backend/utils/adt/formatting.c | 41 +- src/test/regress/expected/int8.out | 176 +++++++ src/test/regress/expected/numeric.out | 416 ++++++++++++++++ src/test/regress/expected/timestamp.out | 604 ++++++++++++++++++++++++ src/test/regress/sql/int8.sql | 28 ++ src/test/regress/sql/numeric.sql | 49 ++ src/test/regress/sql/timestamp.sql | 45 ++ 7 files changed, 1344 insertions(+), 15 deletions(-) diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index 7281d4f4dc..c324014669 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -1,7 +1,7 @@ /* ----------------------------------------------------------------------- * formatting.c * - * $Header: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v 1.4 2000/02/16 17:24:48 thomas Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v 1.5 2000/03/08 01:34:36 momjian Exp $ * * * Portions Copyright (c) 1999-2000, PostgreSQL, Inc @@ -630,7 +630,7 @@ static FormatNode *NUM_cache( int len, char *CacheStr, FormatNode *CacheFormat, static char *int_to_roman(int number); static void NUM_prepare_locale(NUMProc *Np); static char *get_last_relevant_decnum(char *num); -static void NUM_numpart_from_char(NUMProc *Np, int id); +static void NUM_numpart_from_char(NUMProc *Np, int id, int plen); static void NUM_numpart_to_char(NUMProc *Np, int id); static char *NUM_processor (FormatNode *node, NUMDesc *Num, char *inout, char *number, int plen, int sign, int type); @@ -1798,14 +1798,14 @@ timestamp_to_char(Timestamp *dt, text *fmt) char *str; double fsec; char *tzn; - int len=0, tz; + int len=0, tz, x=0; if ((!PointerIsValid(dt)) || (!PointerIsValid(fmt))) return NULL; len = VARSIZE(fmt) - VARHDRSZ; - if (!len) + if ((!len) || (TIMESTAMP_NOT_FINITE(*dt))) return textin(""); tm->tm_sec =0; tm->tm_year =0; @@ -1814,16 +1814,19 @@ timestamp_to_char(Timestamp *dt, text *fmt) tm->tm_mday =1; tm->tm_isdst =0; tm->tm_mon =1; - if (TIMESTAMP_IS_EPOCH(*dt)) - { - timestamp2tm(SetTimestamp(*dt), NULL, tm, &fsec, NULL); + if (TIMESTAMP_IS_EPOCH(*dt)) { + x = timestamp2tm(SetTimestamp(*dt), NULL, tm, &fsec, NULL); + } else if (TIMESTAMP_IS_CURRENT(*dt)) { - timestamp2tm(SetTimestamp(*dt), &tz, tm, &fsec, &tzn); + x = timestamp2tm(SetTimestamp(*dt), &tz, tm, &fsec, &tzn); + } else { - if (timestamp2tm(*dt, &tz, tm, &fsec, &tzn) != 0) - elog(ERROR, "to_char(): Unable to convert timestamp to tm"); + x = timestamp2tm(*dt, &tz, tm, &fsec, &tzn); } + if (x!=0) + elog(ERROR, "to_char(): Unable to convert timestamp to tm"); + tm->tm_wday = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + 1) % 7; tm->tm_yday = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(tm->tm_year, 1,1) +1; @@ -2330,7 +2333,7 @@ get_last_relevant_decnum(char *num) char *result, *p = strchr(num, '.'); - elog(NOTICE, "CALL: get_last_relevant_decnum()"); + /*elog(NOTICE, "CALL: get_last_relevant_decnum()");*/ if (!p) p = num; @@ -2349,16 +2352,21 @@ get_last_relevant_decnum(char *num) * ---------- */ static void -NUM_numpart_from_char(NUMProc *Np, int id) +NUM_numpart_from_char(NUMProc *Np, int id, int plen) { #ifdef DEBUG_TO_FROM_CHAR elog(DEBUG_elog_output, " --- scan start --- "); #endif +#define OVERLOAD_TEST (Np->inout_p >= Np->inout + plen) + if (*Np->inout_p == ' ') Np->inout_p++; + if (OVERLOAD_TEST) + return; + /* ---------- * read sign * ---------- @@ -2413,6 +2421,9 @@ NUM_numpart_from_char(NUMProc *Np, int id) } } + if (OVERLOAD_TEST) + return; + /* ---------- * read digit * ---------- @@ -2430,7 +2441,7 @@ NUM_numpart_from_char(NUMProc *Np, int id) #ifdef DEBUG_TO_FROM_CHAR elog(DEBUG_elog_output, "Read digit (%c).", *Np->inout_p); -#endif +#endif /* ---------- * read decimal point @@ -2821,7 +2832,7 @@ NUM_processor (FormatNode *node, NUMDesc *Num, char *inout, char *number, * Check non-string inout end * ---------- */ - if (Np->inout_p == Np->inout + plen) + if (Np->inout_p >= Np->inout + plen) break; } @@ -2847,7 +2858,7 @@ NUM_processor (FormatNode *node, NUMDesc *Num, char *inout, char *number, NUM_numpart_to_char(Np, n->key->id); continue; /* for() */ } else { - NUM_numpart_from_char(Np, n->key->id); + NUM_numpart_from_char(Np, n->key->id, plen); break; /* switch() case: */ } diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out index 24c58f01d7..c1e26d816a 100644 --- a/src/test/regress/expected/int8.out +++ b/src/test/regress/expected/int8.out @@ -117,3 +117,179 @@ SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL; | 9135780246913578 (5 rows) +-- TO_CHAR() +-- +SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999') + FROM INT8_TBL; + to_char_1 | to_char | to_char +-----------+------------------------+------------------------ + | 123 | 456 + | 123 | 4,567,890,123,456,789 + | 4,567,890,123,456,789 | 123 + | 4,567,890,123,456,789 | 4,567,890,123,456,789 + | 4,567,890,123,456,789 | -4,567,890,123,456,789 +(5 rows) + +SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999') + FROM INT8_TBL; + to_char_2 | to_char | to_char +-----------+--------------------------------+-------------------------------- + | 123.000,000 | 456.000,000 + | 123.000,000 | 4,567,890,123,456,789.000,000 + | 4,567,890,123,456,789.000,000 | 123.000,000 + | 4,567,890,123,456,789.000,000 | 4,567,890,123,456,789.000,000 + | 4,567,890,123,456,789.000,000 | -4,567,890,123,456,789.000,000 +(5 rows) + +SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR') + FROM INT8_TBL; + to_char_3 | to_char | to_char +-----------+--------------------+------------------------ + | <123> | <456.000> + | <123> | <4567890123456789.000> + | <4567890123456789> | <123.000> + | <4567890123456789> | <4567890123456789.000> + | <4567890123456789> | 4567890123456789.000 +(5 rows) + +SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999') + FROM INT8_TBL; + to_char_4 | to_char | to_char +-----------+-------------------+------------------- + | 123- | -456 + | 123- | -4567890123456789 + | 4567890123456789- | -123 + | 4567890123456789- | -4567890123456789 + | 4567890123456789- | +4567890123456789 +(5 rows) + +SELECT '' AS to_char_5, to_char(q2, 'MI9999999999999999') FROM INT8_TBL; + to_char_5 | to_char +-----------+-------------------- + | 456 + | 4567890123456789 + | 123 + | 4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_6, to_char(q2, 'FMS9999999999999999') FROM INT8_TBL; + to_char_6 | to_char +-----------+------------------- + | +456 + | +4567890123456789 + | +123 + | +4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_7, to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL; + to_char_7 | to_char +-----------+-------------------- + | 456TH + | 4567890123456789TH + | 123RD + | 4567890123456789TH + | <4567890123456789> +(5 rows) + +SELECT '' AS to_char_8, to_char(q2, 'SG9999999999999999th') FROM INT8_TBL; + to_char_8 | to_char +-----------+--------------------- + | + 456th + | +4567890123456789th + | + 123rd + | +4567890123456789th + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_9, to_char(q2, '0999999999999999') FROM INT8_TBL; + to_char_9 | to_char +-----------+------------------- + | 0000000000000456 + | 4567890123456789 + | 0000000000000123 + | 4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999') FROM INT8_TBL; + to_char_10 | to_char +------------+------------------- + | +0000000000000456 + | +4567890123456789 + | +0000000000000123 + | +4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999') FROM INT8_TBL; + to_char_11 | to_char +------------+------------------- + | 0000000000000456 + | 4567890123456789 + | 0000000000000123 + | 4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL; + to_char_12 | to_char +------------+----------------------- + | 456.000 + | 4567890123456789.000 + | 123.000 + | 4567890123456789.000 + | -4567890123456789.000 +(5 rows) + +SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL; + to_char_13 | to_char +------------+------------------------ + | 456.000 + | 4567890123456789.000 + | 123.000 + | 4567890123456789.000 + | -4567890123456789.000 +(5 rows) + +SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL; + to_char_14 | to_char +------------+------------------- + | 456 + | 4567890123456789 + | 123 + | 4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL; + to_char_15 | to_char +------------+------------------------------------------- + | +4 5 6 . 0 0 0 + | + 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 + | +1 2 3 . 0 0 0 + | + 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 + | - 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 +(5 rows) + +SELECT '' AS to_char_16, to_char(q2, '99999 text 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL; + to_char_16 | to_char +------------+----------------------------------------------------------- + | text 9999 "text between quote marks" 456 + | 45678 text 9012 9999 345 "text between quote marks" 6789 + | text 9999 "text between quote marks" 123 + | 45678 text 9012 9999 345 "text between quote marks" 6789 + | -45678 text 9012 9999 345 "text between quote marks" 6789 +(5 rows) + +SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL; + to_char_17 | to_char +------------+------------------- + | + 456 + | 456789+0123456789 + | + 123 + | 456789+0123456789 + | 456789-0123456789 +(5 rows) + diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out index 1f7901c701..35ec455754 100644 --- a/src/test/regress/expected/numeric.out +++ b/src/test/regress/expected/numeric.out @@ -694,3 +694,419 @@ SELECT * FROM fract_only; (6 rows) DROP TABLE fract_only; +-- TO_CHAR() +-- +SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999') + FROM num_data; + to_char_1 | to_char +-----------+------------------------ + | 0 + | 0 + | -34,338,492 + | 4 + | 7,799,461 + | 16,397 + | 93,902 + | -83,028,485 + | 74,881 + | -24,926,804 +(10 rows) + +SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999') + FROM num_data; + to_char_2 | to_char +-----------+-------------------------------------------- + | .000,000,000,000,000 + | .000,000,000,000,000 + | -34,338,492.215,397,047,000,000 + | 4.310,000,000,000,000 + | 7,799,461.411,900,000,000,000 + | 16,397.038,491,000,000,000 + | 93,901.577,630,260,000,000 + | -83,028,485.000,000,000,000,000 + | 74,881.000,000,000,000,000 + | -24,926,804.045,047,420,000,000 +(10 rows) + +SELECT '' AS to_char_3, to_char(val, '9999999999999999.999999999999999PR') + FROM num_data; + to_char_3 | to_char +-----------+------------------------------------ + | .000000000000000 + | .000000000000000 + | <34338492.215397047000000> + | 4.310000000000000 + | 7799461.411900000000000 + | 16397.038491000000000 + | 93901.577630260000000 + | <83028485.000000000000000> + | 74881.000000000000000 + | <24926804.045047420000000> +(10 rows) + +SELECT '' AS to_char_4, to_char(val, '9999999999999999.999999999999999S') + FROM num_data; + to_char_4 | to_char +-----------+----------------------------------- + | .000000000000000+ + | .000000000000000+ + | 34338492.215397047000000- + | 4.310000000000000+ + | 7799461.411900000000000+ + | 16397.038491000000000+ + | 93901.577630260000000+ + | 83028485.000000000000000- + | 74881.000000000000000+ + | 24926804.045047420000000- +(10 rows) + +SELECT '' AS to_char_5, to_char(val, 'MI9999999999999999.999999999999999') FROM num_data; + to_char_5 | to_char +-----------+------------------------------------ + | .000000000000000 + | .000000000000000 + | - 34338492.215397047000000 + | 4.310000000000000 + | 7799461.411900000000000 + | 16397.038491000000000 + | 93901.577630260000000 + | - 83028485.000000000000000 + | 74881.000000000000000 + | - 24926804.045047420000000 +(10 rows) + +SELECT '' AS to_char_6, to_char(val, 'FMS9999999999999999.999999999999999') FROM num_data; + to_char_6 | to_char +-----------+--------------------- + | +0. + | +0. + | -34338492.215397047 + | +4.31 + | +7799461.4119 + | +16397.038491 + | +93901.57763026 + | -83028485 + | +74881 + | -24926804.04504742 +(10 rows) + +SELECT '' AS to_char_7, to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data; + to_char_7 | to_char +-----------+---------------------- + | 0. + | 0. + | <34338492.215397047> + | 4.31 + | 7799461.4119 + | 16397.038491 + | 93901.57763026 + | <83028485> + | 74881 + | <24926804.04504742> +(10 rows) + +SELECT '' AS to_char_8, to_char(val, 'SG9999999999999999.999999999999999th') FROM num_data; + to_char_8 | to_char +-----------+----------------------------------- + | + .000000000000000 + | + .000000000000000 + | - 34338492.215397047000000 + | + 4.310000000000000 + | + 7799461.411900000000000 + | + 16397.038491000000000 + | + 93901.577630260000000 + | - 83028485.000000000000000 + | + 74881.000000000000000 + | - 24926804.045047420000000 +(10 rows) + +SELECT '' AS to_char_9, to_char(val, '0999999999999999.999999999999999') FROM num_data; + to_char_9 | to_char +-----------+----------------------------------- + | 0000000000000000.000000000000000 + | 0000000000000000.000000000000000 + | -0000000034338492.215397047000000 + | 0000000000000004.310000000000000 + | 0000000007799461.411900000000000 + | 0000000000016397.038491000000000 + | 0000000000093901.577630260000000 + | -0000000083028485.000000000000000 + | 0000000000074881.000000000000000 + | -0000000024926804.045047420000000 +(10 rows) + +SELECT '' AS to_char_10, to_char(val, 'S0999999999999999.999999999999999') FROM num_data; + to_char_10 | to_char +------------+----------------------------------- + | +0000000000000000.000000000000000 + | +0000000000000000.000000000000000 + | -0000000034338492.215397047000000 + | +0000000000000004.310000000000000 + | +0000000007799461.411900000000000 + | +0000000000016397.038491000000000 + | +0000000000093901.577630260000000 + | -0000000083028485.000000000000000 + | +0000000000074881.000000000000000 + | -0000000024926804.045047420000000 +(10 rows) + +SELECT '' AS to_char_11, to_char(val, 'FM0999999999999999.999999999999999') FROM num_data; + to_char_11 | to_char +------------+----------------------------- + | 0000000000000000. + | 0000000000000000. + | -0000000034338492.215397047 + | 0000000000000004.31 + | 0000000007799461.4119 + | 0000000000016397.038491 + | 0000000000093901.57763026 + | -0000000083028485 + | 0000000000074881 + | -0000000024926804.04504742 +(10 rows) + +SELECT '' AS to_char_12, to_char(val, 'FM9999999999999999.099999999999999') FROM num_data; + to_char_12 | to_char +------------+--------------------- + | .0 + | .0 + | -34338492.215397047 + | 4.31 + | 7799461.4119 + | 16397.038491 + | 93901.57763026 + | -83028485.0 + | 74881.0 + | -24926804.04504742 +(10 rows) + +SELECT '' AS to_char_13, to_char(val, 'FM9999999999990999.990999999999999') FROM num_data; + to_char_13 | to_char +------------+--------------------- + | 0000.000 + | 0000.000 + | -34338492.215397047 + | 0004.310 + | 7799461.4119 + | 16397.038491 + | 93901.57763026 + | -83028485.000 + | 74881.000 + | -24926804.04504742 +(10 rows) + +SELECT '' AS to_char_14, to_char(val, 'FM0999999999999999.999909999999999') FROM num_data; + to_char_14 | to_char +------------+----------------------------- + | 0000000000000000.00000 + | 0000000000000000.00000 + | -0000000034338492.215397047 + | 0000000000000004.31000 + | 0000000007799461.41190 + | 0000000000016397.038491 + | 0000000000093901.57763026 + | -0000000083028485.00000 + | 0000000000074881.00000 + | -0000000024926804.04504742 +(10 rows) + +SELECT '' AS to_char_15, to_char(val, 'FM9999999990999999.099999999999999') FROM num_data; + to_char_15 | to_char +------------+--------------------- + | 0000000.0 + | 0000000.0 + | -34338492.215397047 + | 0000004.31 + | 7799461.4119 + | 0016397.038491 + | 0093901.57763026 + | -83028485.0 + | 0074881.0 + | -24926804.04504742 +(10 rows) + +SELECT '' AS to_char_16, to_char(val, 'L9999999999999999.099999999999999') FROM num_data; + to_char_16 | to_char +------------+------------------------------------ + | .000000000000000 + | .000000000000000 + | -34338492.215397047000000 + | 4.310000000000000 + | 7799461.411900000000000 + | 16397.038491000000000 + | 93901.577630260000000 + | -83028485.000000000000000 + | 74881.000000000000000 + | -24926804.045047420000000 +(10 rows) + +SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999') FROM num_data; + to_char_17 | to_char +------------+--------------------- + | 0. + | 0. + | -34338492.215397047 + | 4.31 + | 7799461.4119 + | 16397.038491 + | 93901.57763026 + | -83028485 + | 74881 + | -24926804.04504742 +(10 rows) + +SELECT '' AS to_char_15, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data; + to_char_15 | to_char +------------+----------------------------------------------------------------------- + | . +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 + | . +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 + | -3 4 3 3 8 4 9 2 . 2 1 5 3 9 7 0 4 7 0 0 0 0 0 0 0 0 + | +4 . 3 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 + | +7 7 9 9 4 6 1 . 4 1 1 9 0 0 0 0 0 0 0 0 0 0 0 0 0 + | +1 6 3 9 7 . 0 3 8 4 9 1 0 0 0 0 0 0 0 0 0 0 0 + | +9 3 9 0 1 . 5 7 7 6 3 0 2 6 0 0 0 0 0 0 0 0 0 + | -8 3 0 2 8 4 8 5 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 + | +7 4 8 8 1 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 + | -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2 0 0 0 0 0 0 0 0 0 +(10 rows) + +SELECT '' AS to_char_16, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data; + to_char_16 | to_char +------------+------------------------------------------------------- + | + 0 . + | + 0 . + | - 3 4 3 3 8 4 9 2 . 2 1 5 3 9 7 0 4 7 + | + 4 . 3 1 + | + 7 7 9 9 4 6 1 . 4 1 1 9 + | + 1 6 3 9 7 . 0 3 8 4 9 1 + | + 9 3 9 0 1 . 5 7 7 6 3 0 2 6 + | - 8 3 0 2 8 4 8 5 + | + 7 4 8 8 1 + | - 2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2 +(10 rows) + +SELECT '' AS to_char_17, to_char(val, '99999 text 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data; + to_char_17 | to_char +------------+----------------------------------------------------------- + | text 9999 "text between quote marks" 0 + | text 9999 "text between quote marks" 0 + | text -3 9999 433 "text between quote marks" 8492 + | text 9999 "text between quote marks" 4 + | text 9999 779 "text between quote marks" 9461 + | text 9999 1 "text between quote marks" 6397 + | text 9999 9 "text between quote marks" 3902 + | text -8 9999 302 "text between quote marks" 8485 + | text 9999 7 "text between quote marks" 4881 + | text -2 9999 492 "text between quote marks" 6804 +(10 rows) + +SELECT '' AS to_char_18, to_char(val, '999999SG9999999999') FROM num_data; + to_char_18 | to_char +------------+------------------- + | + 0 + | + 0 + | - 34338492 + | + 4 + | + 7799461 + | + 16397 + | + 93902 + | - 83028485 + | + 74881 + | - 24926804 +(10 rows) + +SELECT '' AS to_char_19, to_char(val, 'FM9999999999999999.999999999999999') FROM num_data; + to_char_19 | to_char +------------+--------------------- + | 0. + | 0. + | -34338492.215397047 + | 4.31 + | 7799461.4119 + | 16397.038491 + | 93901.57763026 + | -83028485 + | 74881 + | -24926804.04504742 +(10 rows) + +-- TO_NUMBER() +-- +SELECT '' AS to_number_1, to_number('-34,338,492', '99G999G999'); + to_number_1 | to_number +-------------+----------- + | -34338492 +(1 row) + +SELECT '' AS to_number_2, to_number('-34,338,492.654,878', '99G999G999D999G999'); + to_number_2 | to_number +-------------+------------------ + | -34338492.654878 +(1 row) + +SELECT '' AS to_number_3, to_number('<564646.654564>', '999999.999999PR'); + to_number_3 | to_number +-------------+---------------- + | -564646.654564 +(1 row) + +SELECT '' AS to_number_4, to_number('0.00001-', '9.999999S'); + to_number_4 | to_number +-------------+----------- + | -0.00001 +(1 row) + +SELECT '' AS to_number_5, to_number('5.01-', 'FM9.999999S'); + to_number_5 | to_number +-------------+----------- + | -5.01 +(1 row) + +SELECT '' AS to_number_5, to_number('5.01-', 'FM9.999999MI'); + to_number_5 | to_number +-------------+----------- + | -5.01 +(1 row) + +SELECT '' AS to_number_7, to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9'); + to_number_7 | to_number +-------------+----------- + | 544448.78 +(1 row) + +SELECT '' AS to_number_8, to_number('.01', 'FM9.99'); + to_number_8 | to_number +-------------+----------- + | 0.01 +(1 row) + +SELECT '' AS to_number_9, to_number('.0', '99999999.99999999'); + to_number_9 | to_number +-------------+----------- + | 0.0 +(1 row) + +SELECT '' AS to_number_10, to_number('0', '99.99'); + to_number_10 | to_number +--------------+----------- + | 0 +(1 row) + +SELECT '' AS to_number_11, to_number('.-01', 'S99.99'); + to_number_11 | to_number +--------------+----------- + | -0.01 +(1 row) + +SELECT '' AS to_number_12, to_number('.01-', '99.99S'); + to_number_12 | to_number +--------------+----------- + | -0.01 +(1 row) + +SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S'); + to_number_13 | to_number +--------------+----------- + | -0.01 +(1 row) + diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index 37705b615c..efe704a569 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -761,3 +761,607 @@ SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec', | 1 | 0 | 0 (49 rows) +-- TO_CHAR() +-- +SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') + FROM TIMESTAMP_TBL; + to_char_1 | to_char +-----------+------------------------------------------------------------------------------------------ + | + | + | + | THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | + | + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan + | THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun + | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun + | MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb + | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb + | WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb + | THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb + | FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb + | SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb + | SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb + | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb + | SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb + | THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb + | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb + | SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb + | THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb + | TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb + | SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb + | SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb + | WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb + | THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb + | FRIDAY Friday friday FRI Fri fri MARCH March march III MAR Mar mar + | MONDAY Monday monday MON Mon mon DECEMBER December december XII DEC Dec dec + | TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec + | WEDNESDAY Wednesday wednesday WED Wed wed JANUARY January january I JAN Jan jan + | FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb + | SATURDAY Saturday saturday SAT Sat sat MARCH March march III MAR Mar mar + | TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec + | WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec + | FRIDAY Friday friday FRI Fri fri DECEMBER December december XII DEC Dec dec + | SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan + | SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec + | MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan +(62 rows) + + +SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM') + FROM TIMESTAMP_TBL; + to_char_2 | to_char +-----------+-------------------------------------------------------------- + | + | + | + | THURSDAY Thursday thursday JANUARY January january I + | MONDAY Monday monday FEBRUARY February february II + | + | + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | THURSDAY Thursday thursday JANUARY January january I + | THURSDAY Thursday thursday JANUARY January january I + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | TUESDAY Tuesday tuesday JUNE June june VI + | TUESDAY Tuesday tuesday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | MONDAY Monday monday FEBRUARY February february II + | TUESDAY Tuesday tuesday JUNE June june VI + | MONDAY Monday monday FEBRUARY February february II + | TUESDAY Tuesday tuesday FEBRUARY February february II + | WEDNESDAY Wednesday wednesday FEBRUARY February february II + | THURSDAY Thursday thursday FEBRUARY February february II + | FRIDAY Friday friday FEBRUARY February february II + | SATURDAY Saturday saturday FEBRUARY February february II + | SUNDAY Sunday sunday FEBRUARY February february II + | TUESDAY Tuesday tuesday FEBRUARY February february II + | SATURDAY Saturday saturday FEBRUARY February february II + | THURSDAY Thursday thursday FEBRUARY February february II + | TUESDAY Tuesday tuesday FEBRUARY February february II + | SATURDAY Saturday saturday FEBRUARY February february II + | THURSDAY Thursday thursday FEBRUARY February february II + | TUESDAY Tuesday tuesday FEBRUARY February february II + | SUNDAY Sunday sunday FEBRUARY February february II + | SATURDAY Saturday saturday FEBRUARY February february II + | WEDNESDAY Wednesday wednesday FEBRUARY February february II + | THURSDAY Thursday thursday FEBRUARY February february II + | FRIDAY Friday friday MARCH March march III + | MONDAY Monday monday DECEMBER December december XII + | TUESDAY Tuesday tuesday DECEMBER December december XII + | WEDNESDAY Wednesday wednesday JANUARY January january I + | FRIDAY Friday friday FEBRUARY February february II + | SATURDAY Saturday saturday MARCH March march III + | TUESDAY Tuesday tuesday DECEMBER December december XII + | WEDNESDAY Wednesday wednesday DECEMBER December december XII + | FRIDAY Friday friday DECEMBER December december XII + | SATURDAY Saturday saturday JANUARY January january I + | SUNDAY Sunday sunday DECEMBER December december XII + | MONDAY Monday monday JANUARY January january I +(62 rows) + +SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J') + FROM TIMESTAMP_TBL; + to_char_3 | to_char +-----------+------------------------------------------------------- + | + | + | + | 1,970 1970 970 70 0 20 1 01 00 001 01 5 2440588 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | + | + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 01 00 002 02 5 2450451 + | 1,997 1997 997 97 7 20 1 01 00 002 02 5 2450451 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610 + | 2,000 2000 000 00 0 21 1 02 07 046 15 3 2451590 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610 + | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490 + | 1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491 + | 1,997 1997 997 97 7 20 1 02 06 043 12 4 2450492 + | 1,997 1997 997 97 7 20 1 02 06 044 13 5 2450493 + | 1,997 1997 997 97 7 20 1 02 06 045 14 6 2450494 + | 1,997 1997 997 97 7 20 1 02 06 046 15 7 2450495 + | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496 + | 0,097 BC 0097 BC 097 97 7 01 1 02 07 047 16 3 1686042 + | 0,097 0097 097 97 7 01 1 02 06 047 16 7 1756536 + | 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157 + | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778 + | 1,697 1697 697 97 7 17 1 02 06 047 16 7 2340924 + | 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448 + | 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972 + | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496 + | 2,097 2097 097 97 7 21 1 02 06 047 16 7 2487021 + | 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142 + | 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143 + | 1,996 1996 996 96 6 20 1 03 09 061 01 6 2450144 + | 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448 + | 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449 + | 1,997 1997 997 97 7 20 1 01 00 001 01 4 2450450 + | 1,997 1997 997 97 7 20 1 02 08 059 28 6 2450508 + | 1,997 1997 997 97 7 20 1 03 08 060 01 7 2450509 + | 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813 + | 1,997 1997 997 97 7 20 4 12 52 365 31 4 2450814 + | 1,999 1999 999 99 9 20 4 12 52 365 31 6 2451544 + | 2,000 2000 000 00 0 21 1 01 00 001 01 7 2451545 + | 2,000 2000 000 00 0 21 4 12 53 366 31 1 2451910 + | 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911 +(62 rows) + + +SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ') + FROM TIMESTAMP_TBL; + to_char_4 | to_char +-----------+------------------------------------------------- + | + | + | + | 1,970 1970 970 70 0 20 1 1 0 1 1 5 2440588 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | + | + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 1 0 2 2 5 2450451 + | 1,997 1997 997 97 7 20 1 1 0 2 2 5 2450451 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610 + | 2,000 2000 000 00 0 21 1 2 7 46 15 3 2451590 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610 + | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490 + | 1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491 + | 1,997 1997 997 97 7 20 1 2 6 43 12 4 2450492 + | 1,997 1997 997 97 7 20 1 2 6 44 13 5 2450493 + | 1,997 1997 997 97 7 20 1 2 6 45 14 6 2450494 + | 1,997 1997 997 97 7 20 1 2 6 46 15 7 2450495 + | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 + | 0,097 BC 97 BC 097 97 7 1 1 2 7 47 16 3 1686042 + | 0,097 97 097 97 7 1 1 2 6 47 16 7 1756536 + | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157 + | 1,097 1097 097 97 7 11 1 2 7 47 16 3 2121778 + | 1,697 1697 697 97 7 17 1 2 6 47 16 7 2340924 + | 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448 + | 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972 + | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 + | 2,097 2097 097 97 7 21 1 2 6 47 16 7 2487021 + | 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142 + | 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143 + | 1,996 1996 996 96 6 20 1 3 9 61 1 6 2450144 + | 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448 + | 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449 + | 1,997 1997 997 97 7 20 1 1 0 1 1 4 2450450 + | 1,997 1997 997 97 7 20 1 2 8 59 28 6 2450508 + | 1,997 1997 997 97 7 20 1 3 8 60 1 7 2450509 + | 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813 + | 1,997 1997 997 97 7 20 4 12 52 365 31 4 2450814 + | 1,999 1999 999 99 9 20 4 12 52 365 31 6 2451544 + | 2,000 2000 000 00 0 21 1 1 0 1 1 7 2451545 + | 2,000 2000 000 00 0 21 4 12 53 366 31 1 2451910 + | 2,001 2001 001 01 1 21 1 1 1 1 1 2 2451911 +(62 rows) + + +SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') + FROM TIMESTAMP_TBL; + to_char_5 | to_char +-----------+---------------------- + | + | + | + | 12 12 00 00 00 0 + | 05 05 17 32 01 63121 + | + | + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 12 12 00 00 00 0 + | 03 03 03 04 05 11045 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 11 11 23 14 30 83670 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 00 63120 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 09 09 09 32 01 34321 + | 09 09 09 32 01 34321 + | 09 09 09 32 01 34321 + | 06 06 18 32 01 66721 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 + | 05 05 17 32 01 63121 +(62 rows) + +SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text bettween quote marks\\""') + FROM TIMESTAMP_TBL; + to_char_6 | to_char +-----------+-------------------------------------------------- + | + | + | + | HH:MI:SS is 12:00:00 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | + | + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 12:00:00 "text bettween quote marks" + | HH:MI:SS is 03:04:05 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 11:14:30 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:00 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 09:32:01 "text bettween quote marks" + | HH:MI:SS is 09:32:01 "text bettween quote marks" + | HH:MI:SS is 09:32:01 "text bettween quote marks" + | HH:MI:SS is 06:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" + | HH:MI:SS is 05:32:01 "text bettween quote marks" +(62 rows) + + +SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS') + FROM TIMESTAMP_TBL; + to_char_7 | to_char +-----------+------------------------ + | + | + | + | 00--text--00--text--00 + | 17--text--32--text--01 + | + | + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 00--text--00--text--00 + | 03--text--04--text--05 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 23--text--14--text--30 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--00 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 09--text--32--text--01 + | 09--text--32--text--01 + | 09--text--32--text--01 + | 18--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 + | 17--text--32--text--01 +(62 rows) + +SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth') + FROM TIMESTAMP_TBL; + to_char_8 | to_char +-----------+------------------------------- + | + | + | + | 1970TH 1970th 2440588th + | 1997TH 1997th 2450490th + | + | + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450451st + | 1997TH 1997th 2450451st + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450610th + | 2000TH 2000th 2451590th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450610th + | 1997TH 1997th 2450490th + | 1997TH 1997th 2450491st + | 1997TH 1997th 2450492nd + | 1997TH 1997th 2450493rd + | 1997TH 1997th 2450494th + | 1997TH 1997th 2450495th + | 1997TH 1997th 2450496th + | 0097TH BC 0097th BC 1686042nd + | 0097TH 0097th 1756536th + | 0597TH 0597th 1939157th + | 1097TH 1097th 2121778th + | 1697TH 1697th 2340924th + | 1797TH 1797th 2377448th + | 1897TH 1897th 2413972nd + | 1997TH 1997th 2450496th + | 2097TH 2097th 2487021st + | 1996TH 1996th 2450142nd + | 1996TH 1996th 2450143rd + | 1996TH 1996th 2450144th + | 1996TH 1996th 2450448th + | 1996TH 1996th 2450449th + | 1997TH 1997th 2450450th + | 1997TH 1997th 2450508th + | 1997TH 1997th 2450509th + | 1997TH 1997th 2450813rd + | 1997TH 1997th 2450814th + | 1999TH 1999th 2451544th + | 2000TH 2000th 2451545th + | 2000TH 2000th 2451910th + | 2001ST 2001st 2451911st +(62 rows) + +-- TO_TIMESTAMP() +-- +SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS'); + to_timestamp_1 | to_timestamp +----------------+-------------------------- + | Sat Feb 16 08:14:30 0097 +(1 row) + + +SELECT '' AS to_timestamp_2, to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS'); + to_timestamp_2 | to_timestamp +----------------+-------------------------- + | Sat Feb 16 08:14:30 0097 +(1 row) + +SELECT '' AS to_timestamp_3, to_timestamp('1985 January 12', 'YYYY FMMonth DD'); + to_timestamp_3 | to_timestamp +----------------+------------------------------ + | Sat Jan 12 00:00:00 1985 PST +(1 row) + +SELECT '' AS to_timestamp_4, to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16', + '"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD'); + to_timestamp_4 | to_timestamp +----------------+------------------------------ + | Sun May 16 00:00:00 1976 PDT +(1 row) + +SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD'); + to_timestamp_5 | to_timestamp +----------------+-------------------------- + | Sat Aug 21 00:00:00 1582 +(1 row) + +SELECT '' AS to_timestamp_6, to_timestamp('15 "text bettween quote marks" 98 54 45', + 'HH "\\text bettween quote marks\\"" YY MI SS'); + to_timestamp_6 | to_timestamp +----------------+-------------------------- + | Wed Jan 01 15:54:45 0098 +(1 row) + + +SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY'); + to_timestamp_7 | to_timestamp +----------------+------------------------------ + | Fri May 12 14:45:48 2000 PDT +(1 row) + +SELECT '' AS to_timestamp_8, to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay'); + to_timestamp_8 | to_timestamp +----------------+------------------------------ + | Sun Jan 09 00:00:00 2000 PST +(1 row) + diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql index 01de557d60..91ca46b088 100644 --- a/src/test/regress/sql/int8.sql +++ b/src/test/regress/sql/int8.sql @@ -26,3 +26,31 @@ SELECT '' AS five, q1, int8(float8(q1)) AS "two coercions" FROM INT8_TBL; SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL; SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL; + +-- TO_CHAR() +-- +SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999') + FROM INT8_TBL; + +SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999') + FROM INT8_TBL; + +SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR') + FROM INT8_TBL; + +SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999') + FROM INT8_TBL; + +SELECT '' AS to_char_5, to_char(q2, 'MI9999999999999999') FROM INT8_TBL; +SELECT '' AS to_char_6, to_char(q2, 'FMS9999999999999999') FROM INT8_TBL; +SELECT '' AS to_char_7, to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL; +SELECT '' AS to_char_8, to_char(q2, 'SG9999999999999999th') FROM INT8_TBL; +SELECT '' AS to_char_9, to_char(q2, '0999999999999999') FROM INT8_TBL; +SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999') FROM INT8_TBL; +SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999') FROM INT8_TBL; +SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL; +SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL; +SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL; +SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL; +SELECT '' AS to_char_16, to_char(q2, '99999 text 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL; +SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL; diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql index 7e9f333eb9..2f4e4adefd 100644 --- a/src/test/regress/sql/numeric.sql +++ b/src/test/regress/sql/numeric.sql @@ -652,3 +652,52 @@ INSERT INTO fract_only VALUES (7, '0.00001'); INSERT INTO fract_only VALUES (8, '0.00017'); SELECT * FROM fract_only; DROP TABLE fract_only; + +-- TO_CHAR() +-- +SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999') + FROM num_data; + +SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999') + FROM num_data; + +SELECT '' AS to_char_3, to_char(val, '9999999999999999.999999999999999PR') + FROM num_data; + +SELECT '' AS to_char_4, to_char(val, '9999999999999999.999999999999999S') + FROM num_data; + +SELECT '' AS to_char_5, to_char(val, 'MI9999999999999999.999999999999999') FROM num_data; +SELECT '' AS to_char_6, to_char(val, 'FMS9999999999999999.999999999999999') FROM num_data; +SELECT '' AS to_char_7, to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data; +SELECT '' AS to_char_8, to_char(val, 'SG9999999999999999.999999999999999th') FROM num_data; +SELECT '' AS to_char_9, to_char(val, '0999999999999999.999999999999999') FROM num_data; +SELECT '' AS to_char_10, to_char(val, 'S0999999999999999.999999999999999') FROM num_data; +SELECT '' AS to_char_11, to_char(val, 'FM0999999999999999.999999999999999') FROM num_data; +SELECT '' AS to_char_12, to_char(val, 'FM9999999999999999.099999999999999') FROM num_data; +SELECT '' AS to_char_13, to_char(val, 'FM9999999999990999.990999999999999') FROM num_data; +SELECT '' AS to_char_14, to_char(val, 'FM0999999999999999.999909999999999') FROM num_data; +SELECT '' AS to_char_15, to_char(val, 'FM9999999990999999.099999999999999') FROM num_data; +SELECT '' AS to_char_16, to_char(val, 'L9999999999999999.099999999999999') FROM num_data; +SELECT '' AS to_char_17, to_char(val, 'FM9999999999999999.99999999999999') FROM num_data; +SELECT '' AS to_char_15, to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data; +SELECT '' AS to_char_16, to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data; +SELECT '' AS to_char_17, to_char(val, '99999 text 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data; +SELECT '' AS to_char_18, to_char(val, '999999SG9999999999') FROM num_data; +SELECT '' AS to_char_19, to_char(val, 'FM9999999999999999.999999999999999') FROM num_data; + +-- TO_NUMBER() +-- +SELECT '' AS to_number_1, to_number('-34,338,492', '99G999G999'); +SELECT '' AS to_number_2, to_number('-34,338,492.654,878', '99G999G999D999G999'); +SELECT '' AS to_number_3, to_number('<564646.654564>', '999999.999999PR'); +SELECT '' AS to_number_4, to_number('0.00001-', '9.999999S'); +SELECT '' AS to_number_5, to_number('5.01-', 'FM9.999999S'); +SELECT '' AS to_number_5, to_number('5.01-', 'FM9.999999MI'); +SELECT '' AS to_number_7, to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9'); +SELECT '' AS to_number_8, to_number('.01', 'FM9.99'); +SELECT '' AS to_number_9, to_number('.0', '99999999.99999999'); +SELECT '' AS to_number_10, to_number('0', '99.99'); +SELECT '' AS to_number_11, to_number('.-01', 'S99.99'); +SELECT '' AS to_number_12, to_number('.01-', '99.99S'); +SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S'); diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index 264d40a876..5fc4606606 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -167,3 +167,48 @@ SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec', date_part( 'usec', d1) AS usec FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; +-- TO_CHAR() +-- +SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') + FROM TIMESTAMP_TBL; + +SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM') + FROM TIMESTAMP_TBL; + +SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J') + FROM TIMESTAMP_TBL; + +SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ') + FROM TIMESTAMP_TBL; + +SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') + FROM TIMESTAMP_TBL; + +SELECT '' AS to_char_6, to_char(d1, '"HH:MI:SS is" HH:MI:SS "\\"text bettween quote marks\\""') + FROM TIMESTAMP_TBL; + +SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS') + FROM TIMESTAMP_TBL; + +SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth') + FROM TIMESTAMP_TBL; + +-- TO_TIMESTAMP() +-- +SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS'); + +SELECT '' AS to_timestamp_2, to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS'); + +SELECT '' AS to_timestamp_3, to_timestamp('1985 January 12', 'YYYY FMMonth DD'); + +SELECT '' AS to_timestamp_4, to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16', + '"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD'); + +SELECT '' AS to_timestamp_5, to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD'); + +SELECT '' AS to_timestamp_6, to_timestamp('15 "text bettween quote marks" 98 54 45', + 'HH "\\text bettween quote marks\\"" YY MI SS'); + +SELECT '' AS to_timestamp_7, to_timestamp('05121445482000', 'MMDDHHMISSYYYY'); + +SELECT '' AS to_timestamp_8, to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay'); -- 2.40.0