Supabase Backend as a Service · 课时

使用 PostgreSQL 扩展

了解如何启用和使用功能强大的 PostgreSQL 扩展，以添加 UUID 生成、全文搜索等新功能

使用 PostgreSQL 扩展 是 CoddyKit 上的免费 Supabase Backend as a Service 课时。这是第 1 节课，共 3 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Supabase Backend as a Service 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Supabase Backend as a Service 课程共包含 3 节课。

本课时的部分内容尚未翻译，以英文显示。

What are Postgres Extensions?

PostgreSQL is super powerful, but did you know you can make it even better? That's where extensions come in!

Extensions are like plugins for your database. They add new functions, data types, or operators that aren't available by default.

Supabase, built on PostgreSQL, lets you easily enable many of these extensions to boost your app's capabilities.

Enabling Extensions in Supabase

Turning on an extension in Supabase is straightforward. You usually do it through the SQL Editor in your Supabase Dashboard.

Go to the SQL Editor.
Run the command: CREATE EXTENSION extension_name;
Make sure you have admin privileges for your database.

Once enabled, the new features become available for use across your database.

CREATE EXTENSION "uuid-ossp"; -- Example for UUIDs

Unique IDs with `uuid-ossp`

Generating truly unique identifiers is crucial for many applications. PostgreSQL's built-in SERIAL or BIGINT sequences are great for simple auto-incrementing IDs, but they are sequential.

For global uniqueness, especially in distributed systems, Universally Unique Identifiers (UUIDs) are better. The uuid-ossp extension helps generate them.

Using `uuid-ossp`

After enabling uuid-ossp, you can create a table with a UUID primary key. Here's how to create a table and insert a row with a generated UUID:

-- First, ensure the extension is enabled (run once in SQL Editor)
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  price NUMERIC
);

INSERT INTO products (name, price) VALUES
('Supabase T-Shirt', 25.00),
('Postgres Mug', 12.50);

SELECT id, name FROM products;

Fuzzy Search with `pg_trgm`

Have you ever searched for something and misspelled it, but still got relevant results? That's fuzzy search!

The pg_trgm extension helps you perform similarity searches based on trigrams (sequences of three characters). It's great for "did you mean?" features or finding close matches.

Using `pg_trgm` for Similarity

Let's enable pg_trgm and see how to find products with names similar to a search term. We'll use the similarity() function.

-- First, ensure the extension is enabled (run once in SQL Editor)
-- CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create a sample table if it doesn't exist
CREATE TABLE IF NOT EXISTS articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL
);

TRUNCATE TABLE articles; -- Clear previous data for fresh run
INSERT INTO articles (title) VALUES
('PostgreSQL Best Practices'),
('Supabase Authentication Guide'),
('Understanding Postgres Extensions'),
('Postgres Performance Tips');

-- Find titles similar to 'Postgres Extension'
SELECT title, similarity(title, 'Postgres Extension') AS score
FROM articles
WHERE similarity(title, 'Postgres Extension') > 0.3
ORDER BY score DESC;

Key-Value with `hstore`

Sometimes, you need to store flexible, unstructured data, like user preferences or product attributes, without creating many new columns.

The hstore extension provides a data type for storing sets of key/value pairs within a single column. It's like a mini-JSON object, but optimized for simple string key-value storage.

Using `hstore`

Let's add an hstore column to a table and store some flexible data. You can then query it using operators like -> to get a value by key.

-- First, ensure the extension is enabled (run once in SQL Editor)
-- CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  settings HSTORE
);

TRUNCATE TABLE users; -- Clear previous data for fresh run
INSERT INTO users (name, settings) VALUES
('Alice', 'theme=>dark, notifications=>true'),
('Bob', 'theme=>light, language=>en-US');

-- Query for users with dark theme
SELECT name, settings->'theme' AS user_theme
FROM users
WHERE settings->'theme' = 'dark';

Why Use Extensions?

PostgreSQL extensions offer several key benefits:

Extended Functionality: Add powerful features not available by default.
Performance: Many are highly optimized C functions, offering great speed.
Simplicity: Integrate complex logic directly into your database.

However, be mindful of over-reliance and ensure the extension is well-maintained and compatible with your Postgres version.

Extension Check

You've learned about a few powerful PostgreSQL extensions. Let's test your understanding!

Recap: Power Up Your Database!

In this lesson, we explored the world of PostgreSQL extensions! We learned that they are like powerful plugins that enhance your database's capabilities.

We saw how to enable extensions in Supabase.
We used uuid-ossp for generating unique IDs.
We used pg_trgm for fuzzy search.
We used hstore for flexible key-value storage.

These extensions help you build more robust and feature-rich applications directly within your database!

常见问题解答

「使用 PostgreSQL 扩展」课时是免费的吗？

是的 — 「使用 PostgreSQL 扩展」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Supabase Backend as a Service 课程的其余内容，请升级到 CoddyKit PRO。 Supabase Backend as a Service 课程共包含 3 节课。

「使用 PostgreSQL 扩展」这节课中我会学到什么？

了解如何启用和使用功能强大的 PostgreSQL 扩展，以添加 UUID 生成、全文搜索等新功能你通过在浏览器中直接运行的动手代码来练习 Supabase Backend as a Service，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Supabase Backend as a Service 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Supabase Backend as a Service 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 3 节。

「使用 PostgreSQL 扩展」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Supabase Backend as a Service 课中编写并运行代码吗？

能。每节 Supabase Backend as a Service 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Supabase Backend as a Service