Subject: Re: Allow to_date() and to_timestamp() to accept localized names
To: =?UTF-8?Q?Juan_Jos=c3=a9_Santamar=c3=ada_Flecha?=
 <juanjo.santamaria@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
References: <20200124161814.GA8989@alvherre.pgsql>
 <32487.1579882979@sss.pgh.pa.us>
 <2f83150e-a2c0-6318-3125-d9b86e421aa2@2ndquadrant.com>
 <964.1579884408@sss.pgh.pa.us>
 <CAC+AXB1+dHo_DhCTySrqoVaq9WUta9uA+2Ens-bEqDw8X4UbTA@mail.gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Arthur Zakirov <zaartur@gmail.com>,
 Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <ba83b5a7-e63e-5aaf-ac29-745a549f9d7d@2ndquadrant.com>
Date: Fri, 24 Jan 2020 19:01:41 +0100
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: 
 <CAC+AXB1+dHo_DhCTySrqoVaq9WUta9uA+2Ens-bEqDw8X4UbTA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 2020-01-24 18:25, Juan José Santamaría Flecha wrote:
> To illustrate the issue, it does not work as expected:
> 
> postgres=# select lower(to_char(now(),'TMMONTH'));
>     lower
> ------------
>   ιανουάριοσ
> (1 row)
> 
> postgres=# select to_char(now(),'TMmonth');
>    to_char
> ------------
>   ιανουάριος
> (1 row)

Well, this is interesting, because on macOS and Debian stable I get

postgres=# select to_char(now(),'TMmonth');
   to_char
------------
  ιανουαρίου
(1 row)

which is the genitive of ιανουάριος.  You use the genitive form for a 
date (24th of January) but the nominative otherwise.  But the reverse 
mapping can only take one of these forms.  So here

select to_date('Ιανουαριος', 'TMMonth');

fails, which is bad.

In the glibc locale data sources they have both forms listed, but 
apparently the API were are using only accepts one of them.

(I don't know any Greek, I'm just extrapolating from Wikipedia and 
locale data.)

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services