GIN index for arrays and JSONB in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a GIN index on a JSONB column named 'data'.

PostgreSQL

CREATE INDEX idx_data_gin ON my_table USING [1] (data);

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

Agist

Bhash

Cbtree

Dgin

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree index type which does not support JSONB efficiently.

Using hash index which is not suitable for JSONB.

Using gist index which is less efficient for JSONB arrays.

The GIN index type is used for indexing JSONB columns efficiently in PostgreSQL.

2fill in blank

medium

Complete the code to create a GIN index on an array column named 'tags'.

PostgreSQL

CREATE INDEX idx_tags_gin ON articles USING gin([1]);

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

AARRAY[tags]

Btags[]

Ctags

Dtags::text

Attempts:

3 left

💡 Hint

Common Mistakes

Adding brackets or casting the column which causes syntax errors.

Using ARRAY[] syntax which is invalid in this context.

The column name 'tags' is used directly inside the index definition for GIN indexing of arrays.

3fill in blank

hard

Fix the error in the code to create a GIN index on a JSONB column 'info'.

PostgreSQL

CREATE INDEX idx_info_gin ON users USING gin (info [1]);

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

Ajsonb_ops

Bjsonb_path_ops

Cjson_ops

Djsonb_index_ops

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'json_ops' which is invalid for JSONB.

Using 'jsonb_index_ops' which does not exist.

The 'jsonb_ops' operator class is the default and supports all JSONB queries.

4fill in blank

hard

Fill both blanks to create a GIN index on a JSONB column 'attributes' with a specific operator class.

PostgreSQL

CREATE INDEX idx_attributes_gin ON products USING [1] (attributes [2]);

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

Agin

Bbtree

Cjsonb_path_ops

Dgist

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree or gist index types which are not suitable.

Omitting the operator class or using an invalid one.

Use 'gin' as the index type and 'jsonb_path_ops' as the operator class for efficient JSONB indexing.

5fill in blank

hard

Fill all three blanks to create a GIN index on an array column 'keywords' with a custom operator class.

PostgreSQL

CREATE INDEX idx_keywords_gin ON documents USING [1] ([2] [3]);

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

Agin

Bkeywords

Carray_ops

Dbtree

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree index type which is invalid for arrays.

Using wrong operator class or omitting it.

Use 'gin' as the index type, the column name 'keywords', and the operator class 'array_ops' for integer arrays.