Use JSONB operators with GIN indexes, array types, range types for overlap queries, domain types for reusable constraints, and enum types.
0 / 5 completed
1 / 5
What JSONB operators does PostgreSQL provide for querying JSON data and how do they differ from JSON?
JSONB operators:data @> '{"status": "active"}' uses a GIN index to find rows where the JSON contains that key-value pair — O(log n) rather than a full table scan. data ->> 'name' extracts the name as text. JSONB stores a decomposed binary representation, enabling indexing; JSON stores raw text, enabling only sequential search.
2 / 5
What are PostgreSQL array types and what operators do they support?
PostgreSQL arrays:tags TEXT[] stores a list of tags per row. WHERE tags @> ARRAY['go', 'postgres'] finds rows containing both tags. WHERE 'urgent' = ANY(tags) checks for a single element. GIN indexes make these containment queries efficient. Use arrays for small, bounded lists; use a join table for large or frequently updated collections.
3 / 5
What are range types in PostgreSQL and what problems do they solve?
Range types:availability daterange NOT NULL stores a booking period as a single value. WHERE availability && '[2026-07-01, 2026-07-07)' finds conflicting bookings — far simpler than start_date < end AND end_date > start. The EXCLUDE USING gist (room_id WITH =, period WITH &&) constraint prevents overlapping bookings at the database level.
4 / 5
What is a PostgreSQL domain type and how does it differ from a simple constraint?
Domain types: instead of adding a CHECK constraint to every email column across 20 tables, define CREATE DOMAIN email_address AS TEXT CHECK (VALUE ~* '^[^@]+@[^@]+\.\w+$'). Columns declared as email_address automatically inherit the constraint. Changing the validation rule in one place updates all tables. Domains also work in composite types and function signatures.
5 / 5
What are PostgreSQL enum types and what is their key limitation versus a CHECK constraint?
PostgreSQL enums: enums are stored as 4-byte integers (mapped to labels), so they are compact and fast to compare. However, ALTER TYPE status ADD VALUE 'archived' cannot be rolled back within a transaction — it commits immediately. For frequently changing value sets, a foreign-key reference to a lookup table is more flexible at the cost of a join.