Hash index for equality in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a hash index on the column 'email' in the 'users' table.

PostgreSQL

CREATE INDEX idx_users_email ON users USING [1] (email);

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

Abtree

Bhash

Cgist

Dgin

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'btree' which is default but not the specific hash index.

Choosing 'gist' or 'gin' which are for other types of queries.

The hash index type is used for equality comparisons in PostgreSQL.

2fill in blank

medium

Complete the query to find users with email exactly '[e-mail]' using the hash index.

PostgreSQL

SELECT * FROM users WHERE email [1] '[e-mail]';

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

A=

B!=

CILIKE

DLIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIKE or ILIKE which are for pattern matching and do not use hash indexes.

Using != which is inequality and won't use the hash index.

The equality operator = is used to match exact values, which works efficiently with hash indexes.

3fill in blank

hard

Fix the error in the index creation statement to use a hash index on the 'username' column.

PostgreSQL

CREATE INDEX idx_users_username ON users USING [1] (username);

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

Ahash

Bbtree

Cspgist

Dgin

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'btree' which is default but not a hash index.

Using 'gin' or 'spgist' which are for other index types.

To create a hash index, the hash keyword must be used after USING.

4fill in blank

hard

Fill both blanks to create a hash index on the 'phone' column in the 'contacts' table.

PostgreSQL

CREATE INDEX [1] ON contacts USING [2] (phone);

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

Aidx_contacts_phone_hash

Bidx_contacts_phone_btree

Chash

Dbtree

Attempts:

3 left

💡 Hint

Common Mistakes

Using a btree index name with a hash index type or vice versa.

Using generic or unclear index names.

The index name should be descriptive, like idx_contacts_phone_hash, and the index type for equality is hash.

5fill in blank

hard

Fill all three blanks to create a hash index on the 'zipcode' column and query it for exact match '12345'.

PostgreSQL

CREATE INDEX [1] ON addresses USING [2] (zipcode);
SELECT * FROM addresses WHERE zipcode [3] '12345';

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

Aidx_addresses_zipcode_hash

B=

Chash

Didx_addresses_zipcode_btree

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree index name with hash index type.

Using LIKE or other operators instead of '=' for exact match.

The index name is descriptive, the index type is hash, and the equality operator = is used for exact matching.