language detection column content

Question

I have a Postgresql table that has contents of a column either in English or Spanish Like so:

202707  Redness or swelling on the cheek.
202575  Preguntas acerca de medicamentos antivirales de la gripe. 
202433  Sounds very sick or weak to the triager.
201505  El paciente quiere ser visto.   

I would like to query such that I can get only the English rows or only the Spanish rows.

I have read a bit about PostgreSQL dictionaries, to_tsvector and to_tsquery and I feel like there should be a easy way to identify a string as a English/Spanish.

Answer 1

PostgreSQL doesn't have an in-core natural language identifier. I'm not aware of direct PostgreSQL extensions to do it either. But there are lot of language identifiers out there, and it shouldn't be too hard to map one of them into PostgreSQL.

I would do that using pl/perl or pl/perlu to wrap an existing Perl module, like in this answer. But pl/pythonu would be another good choice, but one I have less personal experience with.

You will probably want to store the determined language in a separate database column, rather than computing it on the fly all the time. Doing it that way also means you can do the detection in the app, rather than in the database.