Bird
0
0

You want to create a partitioned table events partitioned by HASH on user_id with 4 partitions. Which set of commands correctly creates the table and its partitions?

hard📝 Application Q15 of 15
PostgreSQL - Table Partitioning
You want to create a partitioned table events partitioned by HASH on user_id with 4 partitions. Which set of commands correctly creates the table and its partitions?
ACREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3);
BCREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CCREATE TABLE events (id INT, user_id INT) PARTITION BY LIST (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3);
DCREATE TABLE events (id INT, user_id INT) PARTITION BY RANGE (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES FROM (0) TO (1); CREATE TABLE events_p1 PARTITION OF events FOR VALUES FROM (1) TO (2); CREATE TABLE events_p2 PARTITION OF events FOR VALUES FROM (2) TO (3); CREATE TABLE events_p3 PARTITION OF events FOR VALUES FROM (3) TO (4);
Step-by-Step Solution
Solution:

  1. Step 1: Understand HASH partition syntax

    HASH partitions require FOR VALUES WITH (MODULUS n, REMAINDER r) to define partitions.

  2. Step 2: Check each option

    CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3); correctly uses HASH partitioning with modulus 4 and remainders 0 to 3. CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3); uses LIST syntax incorrectly. CREATE TABLE events (id INT, user_id INT) PARTITION BY LIST (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3); uses LIST partitioning, not HASH. CREATE TABLE events (id INT, user_id INT) PARTITION BY RANGE (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES FROM (0) TO (1); CREATE TABLE events_p1 PARTITION OF events FOR VALUES FROM (1) TO (2); CREATE TABLE events_p2 PARTITION OF events FOR VALUES FROM (2) TO (3); CREATE TABLE events_p3 PARTITION OF events FOR VALUES FROM (3) TO (4); uses RANGE partitioning, not HASH.

  3. Final Answer:

    The commands using PARTITION BY HASH (user_id) with FOR VALUES WITH (MODULUS 4, REMAINDER 0-3) -> Option B

  4. Quick Check:

    HASH partitions use MODULUS and REMAINDER [OK]
Quick Trick: HASH partitions use MODULUS and REMAINDER in FOR VALUES WITH clause [OK]
Common Mistakes:
  • Using FOR VALUES IN instead of FOR VALUES WITH for HASH
  • Mixing partition types (LIST or RANGE) with HASH
  • Omitting modulus or remainder values

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes
Advanced Features - Composite types - Quiz 12easy Advanced Features - LISTEN and NOTIFY for pub-sub - Quiz 12easy Advanced PL/pgSQL - Why advanced PL/pgSQL matters - Quiz 6medium Indexing Strategies - Expression indexes - Quiz 1easy PL/pgSQL Fundamentals - Why server-side programming matters - Quiz 13medium PL/pgSQL Fundamentals - Variable declaration and assignment - Quiz 9hard Roles and Security - Password authentication methods - Quiz 15hard Transactions and Concurrency - VACUUM and its importance - Quiz 3easy Triggers in PostgreSQL - INSTEAD OF trigger for views - Quiz 8hard Triggers in PostgreSQL - Trigger for data validation - Quiz 7medium