Introduction to full-
text search
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
Brian Piccolo
Sr. Director, Digital Strategy
Topics
Full Text search
Extending PostgreSQL
Improving full text search with extensions
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
The LIKE operator
_ wildcard: Used to match exactly one character.
% wildcard: Used to match zero or more characters.
SELECT title
FROM film
WHERE title LIKE 'ELF%';
+----------------------+
| title |
+----------------------+
| ELF PARTY |
+----------------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
The LIKE operator
SELECT title
FROM film
WHERE title LIKE '%ELF';
+----------------------+
| title |
+----------------------+
| ENCINO ELF |
| GHOSTBUSTERS ELF |
+----------------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
The LIKE operator
SELECT title
FROM film
WHERE title LIKE '%elf%';
+----------------------+
| title |
+----------------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
LIKE versus full-text search
SELECT title, description
FROM film
WHERE to_tsvector(title) @@ to_tsquery('elf');
+----------------------+
| title |
+----------------------+
| ELF PARTY |
| ENCINO ELF |
| GHOSTBUSTERS ELF |
+----------------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
What is full-text search?
Full text search provides a means for performing natural language queries of text data in
your database.
Stemming
Spelling mistakes
Ranking
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Full-text search syntax explained
SELECT title, description
FROM film
WHERE to_tsvector(title) @@ to_tsquery('elf');
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Let's practice!
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
Extending
PostgreSQL
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
Brian Piccolo
Sr. Director, Digital Strategy
User-defined data types
Enumerated data types
CREATE TYPE dayofweek AS ENUM (
'Monday',
'Tuesday',
'Wednesday',
'Thursday',
'Friday',
'Saturday',
'Sunday'
);
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Getting information about user-defined data types
SELECT typname, typcategory
FROM pg_type
WHERE typname='dayofweek';
+-----------+-------------+
| typname | typcategory |
|-----------|-------------|
| dayofweek | E |
+-----------+-------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Getting information about user-defined data types
SELECT column_name, data_type, udt_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name ='film';
+-----------------------------------------------+
| column_name | data_type | udt_name |
|-------------|-------------------|-------------|
| title | character varying | varchar |
| rating | USER-DEFINED | mpaa_rating |
+-----------------------------------------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
User-defined functions
CREATE FUNCTION squared(i integer) RETURNS integer AS $$
BEGIN
RETURN i * i;
END;
$$ LANGUAGE plpgsql;
SELECT squared(10);
+---------+
| squared |
|---------|
| 100 |
+---------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
User-defined functions in the Sakila database
get_customer_balance(customer_id, e ective_data): calculates the current outstanding
balance for a given customer.
inventory_held_by_customer(inventory_id): returns the customer_id that is currently
renting an inventory item or null if it's currently available.
inventory_in_stock(inventory_id): returns a boolean value of whether an inventory item is
currently in stock.
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Let's practice!
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
Intro to PostreSQL
extensions
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
Brian Piccolo
Sr. Director, Digital Strategy
Intro to PostgreSQL extensions
Commonly used extensions
PostGIS
PostPic
fuzzystrmatch
pg_trgm
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Querying extension meta data
Available Extensions Installed Extensions
SELECT name SELECT extname
FROM pg_available_extensions; FROM pg_extension;
+--------------------+ +---------+
| name | | name |
|--------------------| |---------|
| dblink | | plpgsql |
| pg_stat_statements | +---------+
+--------------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
--Enable the fuzzystrmatch extension
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
--Confirm that fuzzstrmatch has been enabled
SELECT extname FROM pg_extension;
+---------------+
| name |
|---------------|
| plpgsql |
| fuzzystrmatch |
+---------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Using fuzzystrmatch or fuzzy searching
SELECT levenshtein('GUMBO', 'GAMBOL');
+-------------+
| levenshtein |
|-------------|
| 2 |
+-------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Compare two strings with pg_trgm
SELECT similarity('GUMBO', 'GAMBOL');
+------------+
| similarity |
|------------|
| 0.18181818 |
+------------+
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Let's practice!
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
Putting it All
Together
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
Brian Piccolo
Sr. Director, Digital Strategy
Functions for manipulating data recap and review
Common data types in PostgreSQL
Date/time functions and operators
Parsing and manipulating text
PostgreSQL Extensions and full-text search
FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL
Thank you!
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
