string.PadLeft and string.PadRight wrong translation

[ozgur-yalcin]

Npgsql EF Core provider automatically translates stringValue.PadLeft(length, char) -> lpad(stringValue, length, char).

However, these two functions do not produce exactly the same result and may cause problems.

C# example:

        var stringValue = "1234";
        Console.WriteLine(stringValue.PadLeft(2, '0'));
        /* Output : 1234 */

SQL example:

        SELECT lpad('1234', 2, '0')
        /* Output : 12 */

If the string is already longer than length then it is truncated in lpad and rpad functions.
lpad and rpad functions need to be replaced with a new function that gives the correct result.

I tried a method like the one below, but strangely, in a different scenario, I still did not get the result I expected.
SQL example:

        SELECT greatest('1234'::text, lpad('1234'::text, 6, '0')::text)
        /* Output : 1234 */

After working on it a bit I found a method that works correctly as follows

         /* lpad alternative */
        SELECT repeat('0', 6 - length('1234')) || '1234' /* Output : 001234 */
        SELECT repeat('0', 2 - length('1234')) || '1234' /* Output : 1234 */
        
        /* rpad alternative */
        SELECT '1234' || repeat('0', 6 - length('1234')) /* Output : 123400 */
        SELECT '1234' || repeat('0', 2 - length('1234')) /* Output : 1234 */

Do you have any other ideas?

[Voonti]

It seems like, this is intentional. Better question is, what is the origin of padding, to truncated if less, or to return it intact.

[roji]

@ozgur-yalcin sorry for not answering earlier, this is a very busy period.

If the behavior discrepancy is when the string is longer, then we can continue to use lpad/rpad, but wrap it in a CASE/WHEN in the translation to simply return the original string when it's longer, i.e. CASE length(x) >= y THEN x ELSE lpad(x, y, '0'), how does that sound?

@Voonti the point @ozgur-yalcin made above is that .NET's PadLeft and PG lpad don't behave the same way when the string a larger (i.e. PG truncates, .NET does nothing).

[roji]

@ozgur-yalcin if this sounds good, are you interested in submitting a PR to fix this?

[ozgur-yalcin]

Sounds good, thanks for your support.

[ChrisJollyAU]

@roji do you want a test for padleft/right for both with constant and with parameter to check that it doesn't truncate