3

I am trying to use geohash (the manual way as I can't use the geo methods postgres gis has because of the framework I am using).

To do that I have a column in postgres (geoHash text default ''::text not null) to store that value.

Now to the problem: Let's assume my column contains the value c28xzxe29h I try to query that record with the following query: geoHash <= 'c28x~' however it does not return the expected value (select 'c28xzxe29h' <= 'c28x~'; returns False). However, this query does work correctly with firestore and also when executing in for example javascript (in both cases includes the entry/returns true). I also tested it with rust (println!("{}", "c28xzxe29h" <= "c28x~"); that returns true: https://play.rust-lang.org/). So not sure why all programming languages seem to agree but postgres does not.

The question is how can I get postgres to return the expected value (I am using geofire-commons to generate the query ranges that spit out in some cases the ~ character).

fkrauthan
  • 498
  • 4
  • 8

1 Answers1

5

The ordering of strings depends on the collation in use. Different human languages have different ordering rules. Maybe all of those other programming languages you mention use C collation, either because they were configured to do that be default, or because that is the only thing they know how to do.

PostgreSQL will return true if you tell it to use C collation:

select 'c28xzxe29h' <= 'c28x~' collate "C";

You can make this your default collation if you want.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • 1
    Note that collations and locales act differently on the Mac. `select 'c28xzxe29h' <= 'c28x~' collate "en_US"` is true on a Mac whereas that is false on Linux. – Schwern Dec 13 '22 at 20:34
  • OMG, you are a lifesaver. Setting it to "C" works exactly as expected. – fkrauthan Dec 13 '22 at 21:46