Database indexes in Supabase - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an index on the 'email' column in a Supabase PostgreSQL table.

Supabase

CREATE INDEX [1] ON users (email);

Drag options to blanks, or click blank then click option'

Ausers_email_idx

Bemail_index

Cidx_email

Dindex_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved words as index names

Leaving the index name empty

Using spaces in the index name

The index name should be descriptive and unique. 'users_email_idx' follows common naming conventions for indexes.

2fill in blank

medium

Complete the code to create a unique index on the 'username' column to prevent duplicate usernames.

Supabase

CREATE [1] INDEX unique_username ON users (username);

Drag options to blanks, or click blank then click option'

Aunique

Bforeign

Cprimary

Dclustered

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'primary' instead of 'unique'

Using 'foreign' which is for foreign keys

Omitting the keyword for uniqueness

The keyword 'unique' ensures that the index enforces uniqueness on the 'username' column.

3fill in blank

hard

Fix the error in the code to create an index on the 'created_at' column in descending order.

Supabase

CREATE INDEX idx_created_at ON events (created_at [1]);

Drag options to blanks, or click blank then click option'

AASC

BDESC

CDOWN

DREVERSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ASC' which sorts ascending

Using invalid keywords like 'DOWN' or 'REVERSE'

Omitting the order keyword

The correct keyword to specify descending order in PostgreSQL is 'DESC'.

4fill in blank

hard

Fill both blanks to create a partial index on the 'status' column where status is 'active'.

Supabase

CREATE INDEX [1] ON users (status) WHERE status [2] 'active';

Drag options to blanks, or click blank then click option'

Aidx_active_status

B=

C!=

Dactive_status_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' instead of '=' in the condition

Using a non-descriptive index name

Omitting the WHERE clause

The index name 'idx_active_status' is descriptive. The condition uses '=' to filter rows where status is 'active'.

5fill in blank

hard

Fill all three blanks to create a multi-column unique index on 'email' and 'username'.

Supabase

CREATE [1] INDEX [2] ON users ([3], username);

Drag options to blanks, or click blank then click option'

Aunique

Buser_email_username_idx

Cemail

Dprimary

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'primary' instead of 'unique'

Using a generic index name

Swapping column order incorrectly

'unique' enforces uniqueness across both columns. The index name 'user_email_username_idx' is descriptive. The first column is 'email'.