Search configuration and languages in PostgreSQL - Mini Project: Build & Apply

Search Configuration and Languages in PostgreSQL

📖 Scenario: You are building a simple text search feature for a library database. The library has books in different languages, and you want to configure the search to work well with English and French texts.

🎯 Goal: Create a PostgreSQL setup that defines a text search configuration for English and French languages, then use it to search book titles effectively.

📋 What You'll Learn

Create a table called books with columns id (integer) and title (text).

Insert three books with titles in English and French.

Create a text search configuration for English language.

Create a text search configuration for French language.

Write a query that uses the English text search configuration to find books matching a search term.

💡 Why This Matters

🌍 Real World

Libraries, bookstores, and content management systems often need to search text in multiple languages accurately.

💼 Career

Understanding text search configurations is important for database developers and backend engineers working on search features.

1

Create the books table and insert data

Create a table called books with columns id as integer and title as text. Insert these three rows exactly: (1, 'The Great Gatsby'), (2, 'Le Petit Prince'), and (3, 'Pride and Prejudice').

PostgreSQL

-- Create the books table and insert three rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Create English and French text search configurations

Create a text search configuration called english_search based on the built-in english configuration. Also create a text search configuration called french_search based on the built-in french configuration.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Great Gatsby'),
(2, 'Le Petit Prince'),
(3, 'Pride and Prejudice');

-- Create english_search and french_search configurations
-- Your code here

Need a hint?

Use CREATE TEXT SEARCH CONFIGURATION with COPY = english or COPY = french to base on built-in configs.

3

Write a query using English text search configuration

Write a SELECT query to find all books where the title matches the search term 'great' using the english_search text search configuration. Use to_tsvector with english_search on title and to_tsquery with english_search on the search term.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Great Gatsby'),
(2, 'Le Petit Prince'),
(3, 'Pride and Prejudice');

CREATE TEXT SEARCH CONFIGURATION english_search ( COPY = english );
CREATE TEXT SEARCH CONFIGURATION french_search ( COPY = french );

-- Write a SELECT query to find books matching 'great' using english_search
-- Your code here

Need a hint?

Use to_tsvector and to_tsquery with the english_search configuration and the @@ operator to match.

4

Complete by creating a French search query

Write a SELECT query to find all books where the title matches the search term 'petit' using the french_search text search configuration. Use to_tsvector with french_search on title and to_tsquery with french_search on the search term.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Great Gatsby'),
(2, 'Le Petit Prince'),
(3, 'Pride and Prejudice');

CREATE TEXT SEARCH CONFIGURATION english_search ( COPY = english );
CREATE TEXT SEARCH CONFIGURATION french_search ( COPY = french );

SELECT id, title
FROM books
WHERE to_tsvector('english_search', title) @@ to_tsquery('english_search', 'great');

-- Write a SELECT query to find books matching 'petit' using french_search
-- Your code here

Need a hint?

Similar to the English query, but use french_search and the search term 'petit'.