order by

[ghenry]

Hi all,

I presume I can't order by in Pg as the data is encrypted and Pg doesn't know what it is?

Thanks.

[KristerV]

correct. got to order after DB query and if performance is an issue have a cache.

[danielberkompas]

Yes, this is correct. If order is important, I would suggest having an unencrypted field that contains some kind of sortable information that won't reveal the nature of the plaintext.

For example, if you wanted to sort by an encrypted "created at" field, you could add a new auto-incrementing integer field to the database table and sort by that. The auto-increment would ensure that rows with more recent created_at dates have a higher number than older ones, without revealing the exact dates.