Typical Mistakes When Sorting and Formatting Data

Sorting and formatting — it just makes sense and is super useful. But sometimes you can accidentally mess things up and get some weird results. Let’s go through the most common mistakes together so your data always looks perfect and shows up in the right order!

1. Sorting Data of Different Types

Picture this: you’re sorting a table by a column that’s got a mix of numbers and strings. PostgreSQL will try to help, but the result might surprise you. For example, you’ve got a table where value is text:

And you write a query:

SELECT *
FROM mixed_data 
ORDER BY value;

Expected result? Maybe: 2, 10, 20, apple? Nope, PostgreSQL sorts strings based on their lexicographical/alphabetical order, so the result will be: 10, 2, 20, apple.

How to avoid this mistake?

If you know your text column actually has numbers, just cast them to numbers before sorting:

SELECT * FROM mixed_data ORDER BY value::INT;

Now the result will be: 2, 10, 20.

But be careful: if your column has strings that can’t be converted to numbers, your query will throw an error!

2. Specifying Multiple Columns for Sorting

One classic mistake — forgetting about priority order when sorting by several columns. For example, you want to sort students by last name, then by age, but you mix up the order:

Your query:

-- Wrong sorting
SELECT * 
FROM students 
ORDER BY age, last_name;

Result:

Here, PostgreSQL first sorts students by age, then by last name. Not really what you wanted, right?

The correct query:

SELECT *
FROM students 
ORDER BY last_name, age;

Result:

Now sorting goes by last name first, and inside each last name — by age. Exactly what you expected!

3. Sorting with Different Directions

Sometimes you need to sort with different directions for different columns. For example, sort products by category (ascending), and inside each category — by price (descending). The mistake is forgetting to set the direction for the second column:

Your query:

-- Wrong sorting
SELECT * 
FROM products 
ORDER BY category, price;

Result: prices inside categories are sorted ascending, but maybe you wanted them descending.

The correct query:

SELECT *
FROM products 
ORDER BY category ASC, price DESC;

Mistakes When Formatting

4. Incorrect Use of CONCAT()

Let’s say you want to join first and last names into one string, but you forget to add a separator between them:

Your query:

SELECT
    CONCAT(first_name, last_name) AS full_name 
FROM employees;

Result:

How to fix it?

Add a space between first and last name:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Now it’ll look way better:

5. Mistakes When Using CAST()

Let’s say you’re trying to convert a date to a string for pretty output, but you forget to set the format:

Your query:

-- Wrong conversion
SELECT 
    CAST(event_date AS TEXT) 
FROM events;

Result: the date will be converted to YYYY-MM-DD format, which isn’t always user-friendly.

How to fix it?

Use the TO_CHAR() function to set the format:

SELECT
    TO_CHAR(event_date, 'DD-MM-YYYY') AS formatted_date 
FROM events;

Now the date will show up as 15-01-2023.

6. Mistakes When Using DISTINCT

DISTINCT is a powerful tool for getting unique values, but sometimes it’s used wrong. For example, you want to get a list of unique employee names:

SELECT DISTINCT first_name, last_name
FROM employees;

At first glance, the query looks fine, but if two employees have the same first and last names, they’ll show up as one result, even if they’re different people.

Sample data:

Query result:

How to avoid this mistake?

If you really want unique people (not table rows), select by the set of columns you actually want to distinguish records by. For example, via GROUP BY:

SELECT first_name, last_name
FROM employees
GROUP BY first_name, last_name;

Using DISTINCT ON (id), where id is the primary key, is pointless: the PK is already unique, so such a query returns every row, just like a plain SELECT. Remember: DISTINCT always operates over all columns in SELECT — that's semantics, not a bug.

Result of the query with DISTINCT ON (id) (it returns all rows of the table):

How to Avoid Mistakes?

Working with data means paying attention to details. To avoid the mistakes above:

Check your data types: make sure the functions you use support your column data types.

Check your sort order: make sure columns are listed in the right order. Don’t forget about sort directions ASC, DESC.

Test queries on small data samples: this helps catch mistakes early.

Don’t forget about the PostgreSQL docs: they’ll help answer tricky questions about functions and how to use them. Here’s the link: PostgreSQL official docs.

Now you’re ready to tackle any mysteries with formatting and sorting! Let’s keep going.