We're sorry but this page doesn't work properly without JavaScript enabled. Please enable it to continue.
Feedback

Manipulating text with PostgreSQL - lesser known PG jewels

Formal Metadata

Title
Manipulating text with PostgreSQL - lesser known PG jewels
Title of Series
Number of Parts
351
Author
License
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Identifiers
Publisher
Release Date
Language
Production Year2022

Content Metadata

Subject Area
Genre
Abstract
PostgreSQL is the most advanced opensource RDBMS. As GIS folks, you most probably use it in combination with PostGIS, its Geospatial plugin. When dealing with Geospatial data, we usually focus on geometries. But most of feature attributes are text data. Of course, filtering on these text data with standard SQL capabilities is a day-to-day operation for database users. But PostgreSQL provides much more capabilities when it comes down to text data management. In this presentation, we will go through a few of them. After a quick look at standard text functions in PostgreSQL, we will discover the lesser known fuzzy matching modules : - `pg_trgm` extension allows for string searches using trigraphs to determine a similarity rank between text items - `fuzzystrmatch` extension provides fuzzy matching functions like soundex, Levenshtein, metaphone Then, we will explore *Full Text Search ( FTS )* PostgreSQL capabilities. Last but not least, we will peek inside PostgreSQL collation concept, which has nothing to do with your lunch. Collations are a powerful feature in PostgreSQL allowing to adapt the way you deal with text data according to the localization. Like trying to answer this - apparently - obvious question : is '12' before or after '2' ? And, because we can, display all of this on a map :-)
Keywords