CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Amog Kamsetty
Amog Kamsetty

Reputation: 194

Postgres String Comparison with leading spaces

I want Postgres to include leading spaces as part of the string when making string comparisons. However, this is not the case on version 9.5.

select ' s' > 'ny';
 ?column?
----------
 t
(1 row)

I want this to return false, and I thought that it should since the ASCII value for space is less than 'n'. Is there some setting to change so that this can return false?

Upvotes: 2

Views: 1146

Answers (2)

binoternary
binoternary

Reputation: 1915

https://www.postgresql.org/docs/current/collation.html

The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation.
...

When the database system has to perform an ordering or a character classification, it uses the collation of the input expression. This happens, for example, with ORDER BY clauses and function or operator calls such as <. The collation to apply for an ORDER BY clause is simply the collation of the sort key. The collation to apply for a function or operator call is derived from the arguments, as described below. In addition to comparison operators, collations are taken into account by functions that convert between lower and upper case letters, such as lower, upper, and initcap; by pattern matching operators; and by to_char and related functions.

Example of different collation behavior:

postgres=# select ' s' > 'ny' collate "en_GB.utf8";
?column? 
----------
t
(1 row)

postgres=# select ' s' > 'ny' collate "C";
?column? 
----------
f
(1 row)

SELECT * FROM pg_collation; to see available collations.

Upvotes: 2

4ndt3s
4ndt3s

Reputation: 3467

Try with:

select ' s'::bytea > 'ny'::bytea;

I've tested with postgres 11 and it works. See documentation.

Upvotes: 2

Related Questions