Brightlane's compliance team needs a single deduplicated column listing every way a customer can be identified in the system — both their numeric ID and their email address — in one combined list.
Write a query to return one row per unique identifier.
Assumptions:
- The
customerstable contains every customer Brightlane has on file. - The
idcolumn is integer; theemailcolumn is text. Both must appear in the same output column. - The result is deduplicated — if any ID happens to also appear as an email value, it appears once.
Output:
- One row per distinct identifier, with a single column
identifier.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id::TEXT AS identifier
FROM
customers
UNION
SELECT
email
FROM
customersThe shape
The id::text cast is doing all the work. Without it, the left side projects an integer and the right side projects text, and PostgreSQL refuses to stack incompatible types across a set operation. Casting the integer ID to text makes both columns text, the two sides reconcile, and the compliance team gets a single deduplicated identifier column holding both numeric IDs and email addresses.
Clause by clause
SELECT id::text AS identifier FROM customersis the left input. The::textcast converts the integeridinto its text representation —1becomes'1',42becomes'42'. TheAS identifieralias names the output column; the column name comes from the left query, so this is the alias that survives.UNIONis the set operator. PostgreSQL evaluates both queries, then sorts or hashes the combined result to deduplicate.UNION(notUNION ALL) is what gives the compliance team "one row per unique identifier."SELECT email FROM customersis the right input. Theemailcolumn is already text, so no cast is needed. The two sides now agree on column count (one) and column type (text), which is what lets the set operation run.
Why this and not the uncast version
The uncast version (SELECT id ... UNION SELECT email ...) fails outright. PostgreSQL refuses to stack an integer column with a text column across UNION — the error is UNION types integer and text cannot be matched. There is no implicit coercion between numeric and text types for set operations; the engine raises and the query never produces rows.
The cast resolves it because text is the only type that can hold every value the prompt cares about. An email can't be written as an integer, but an integer can be written as text. So the cast goes on the integer side, promoting it to the wider type the text side already speaks.
The trap
The trap is reading the type-mismatch error as a syntax issue and trying to fix it by reordering the queries or removing the alias. Neither helps. The constraint is type compatibility across corresponding column positions, and the fix is an explicit cast on whichever side has the narrower type. The wider type is the destination; the cast is the bridge.
You practiced reconciling type mismatches across a UNION. The recurring rule: corresponding columns from both sides must resolve to a compatible type — when they don't (integer vs text here), UNION raises a type error that an explicit CAST resolves.
