GREATEST and split_part not working as intended in postgresqlp

Question

I am trying to run this query in pgadmin (postgresql) client but not getting expected response:

SELECT GREATEST(split_part('abc | 1 | 5', '|', 3), '0');

I expected the response to be 5 but it is 0. Am I missing anything?

PostgreSQL version is 13.7.

Answer 1

Collation issue:

show lc_collate ;
 lc_collate  
-------------
 en_US.UTF-8

select greatest(' 5', '0'), length(greatest(' 5', '0'));
 greatest | length 
----------+--------
  5       |      2

select greatest('5', '0'), length(greatest('5', '0'));
 greatest | length 
----------+--------
 5        |      1

\c c_db

show lc_collate ;
 lc_collate 
------------
 C

select greatest(' 5', '0'), length(greatest(' 5', '0'));
 greatest | length 
----------+--------
 0        |      1
(1 row)

select greatest('5', '0'), length(greatest('5', '0'));
 greatest | length 
----------+--------
 5        |      1


select greatest(' 5'::int, '0'::int);
 greatest 
----------
        5

If you stay with C collation your choices are:

1. Don't have leading spaces

2. If you know you are always going to be dealing with integer strings then cast to integer.

UPDATE

This behavior is due to how the collation affects the sort(value) order of strings. To demonstrate:

show lc_collate ;
 lc_collate  
-------------
 en_US.UTF-8

create table str_order(fld_1 varchar);
insert into str_order values ('0'), (' 5');
select * from str_order order by fld_1;
 fld_1 
-------
 0
  5

show lc_collate ;
 lc_collate 
------------
 C
create table str_order(fld_1 varchar);
insert into str_order values ('0'), (' 5');

select * from str_order order by fld_1;
 fld_1 
-------
  5
 0

You can force a collation for a given statement:

 show lc_collate ;
 lc_collate 
------------
 C

 select greatest((' 5' collate "en_US"), '0'), length(greatest(' 5', '0'));
 greatest | length 
----------+--------
  5       |      1

See Collation for more information.