SQL
Basic Case-Insensitive Full-Text Search (PostgreSQL)
Implement simple case-insensitive full-text search in PostgreSQL using the `ILIKE` operator for flexible data querying across multiple columns.
-- Sample table
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(255),
content TEXT,
author VARCHAR(100)
);
-- Insert sample data
INSERT INTO articles (title, content, author) VALUES
('Introduction to SQL', 'SQL is a powerful language for managing relational databases. Learn the basics here.', 'Alice'),
('Advanced PostgreSQL Features', 'Explore advanced topics like JSONB, window functions, and full-text search.', 'Bob'),
('Web Development Basics', 'Understand the fundamentals of HTML, CSS, and JavaScript for web development.', 'Charlie');
-- Search for articles containing "sql" (case-insensitive) in title or content
SELECT id, title, author
FROM articles
WHERE title ILIKE '%sql%' OR content ILIKE '%sql%';
-- Search for articles containing "web" and "development" (anywhere)
SELECT id, title, author
FROM articles
WHERE (title ILIKE '%web%' OR content ILIKE '%web%')
AND (title ILIKE '%development%' OR content ILIKE '%development%');
-- More complex search across multiple fields
SELECT id, title, author
FROM articles
WHERE (title ILIKE '%database%' OR content ILIKE '%database%' OR author ILIKE '%alice%');
How it works: This snippet illustrates basic full-text searching using the `ILIKE` operator in PostgreSQL. `ILIKE` performs a case-insensitive pattern match, making it ideal for user searches where exact casing isn't expected. Wildcards (`%`) are used to match any sequence of characters. It demonstrates searching within a single column, across multiple columns, and combining search terms with `AND`/`OR`.