Specify Postgresql DB default collation for a Rails project

Question

I have a Rails 4.2+ and Postgres 9.4 project and I need to change how Postgres orders items in a query. The default settings seem to ignore whitespace and I need a strategy that takes white space into account. After running \l in psql, I see the db has Collate set to en_US.UTF-8 and Ctype set to en_US.UTF-8.

Updated 12/30/15 4:14pm

I have the following Activities with a name column with the following names:

Code1A East
Code1A West
Code1 AEast
Code1 AWest

When I execute the following query in Rails

Activity.order(name: :asc)

I expected to get back a list of Activities in the following order.

Code1 AEast
Code1 AWest 
Code1A East
Code1A West

or maybe even

Code1A East
Code1A West
Code1 AEast
Code1 AWest

Instead I get

Code1A East
Code1 AEast
Code1A West
Code1 AWest

It appears to be disregarding the space in the name. I noted in a comment below, I was able to get the Rails app to create a DB with a specific collation by adding the following to database.yml

development:
  ...other keys...
  encoding: utf8
  collation: sv_SE.UTF-8
  ctype: sv_SE.UTF-8
  template: template0

But I don't know which collation setting will pick a "better" sort order.

Is the ordering PostgreSQL-specific? This SO topic might help. You can specify a default order for each model whenever you run a query. — Alexander
– Alexander, Commented Dec 30, 2015 at 20:38
Please post an example of a query result that doesn't match your expectation. Sorting doesn't ignore the white spaces by default. — Simone Carletti
– Simone Carletti, Commented Dec 30, 2015 at 20:52

Robert Nubel · Accepted Answer · 2015-12-30 20:56:10Z

2

The collation setting on Postgres is determined by the server, not your connection to it, per the Postgres docs, and as such there's no way to make it just a setting in database.yml. To permanently change the value, you'll need to permanently modify your server.

However, you can force a different collation for sorting on a per-query basis, in a couple ways:

SELECT * FROM things ORDER BY name COLLATE "C" ASC;

Will use the C collation, which is probably what you're looking for, and accomplishes the same result as:

SELECT * FROM things ORDER BY name USING ~<~;

(see this SQLfiddle for a live example)

Hope that helps!

answered Dec 30, 2015 at 20:56

Robert Nubel

7,5621 gold badge20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CodeSmith Over a year ago

I just tried adding encoding: utf8, collation: sv_SE.UTF-8, template: template0 to the development group in database.yml, and re-created the database and I can see the new collation settings on the DB.

Robert Nubel Over a year ago

@CodeKing: sure, but that setting will only change when you rebuild the server. If you provision, say, a new database in Heroku or AWS, those settings will likely not be respected (so be cautious when you deploy to production!).

Collectives™ on Stack Overflow

Specify Postgresql DB default collation for a Rails project

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related