Database Reference
In-Depth Information
Interacting only with the static columns
We've seen that static columns give us the ability to associate data with all rows that share
the same partition key. However, what if we want to treat the data associated with that par-
tition key as a discrete value rather than some extra information attached to each clustering
column value?
More concretely, if we want to display an interface for
alice
to edit her user profile, all
we really need to do is get the information associated with her partition key. We don't care
if there are one, ten, or a thousand status updates; we just need a single row with her user-
name, email address, and so on. With
users
as a separate table, this is trivial; we just se-
lect the single row with the partition key value
alice
. Let's see what happens when we do
the equivalent in
users_with_status_updates
:
SELECT "username", "email", "encrypted_password"
FROM "users_with_status_updates"
WHERE "username" = 'alice';
Note that we've omitted the
id
and
body
fields, which contain distinct values in each row;
we only select the partition key—
username
, and the static columns—
email
and
en-
crypted_password
. Unfortunately, we don't quite get what we're looking for:
The result is two rows—the rows contain identical data because all of the selected columns
are per-partition-key, but we still get duplicate results when we would only like one. Hap-
pily, the
DISTINCT
keyword allows us to retrieve a result in the format that we'd like:
SELECT DISTINCT "username", "email", "encrypted_password"
FROM "users_with_status_updates"
WHERE "username" = 'alice';
Now, the result takes the form we'd like—a single row containing the information specific
to
alice
's partition key:
