Checking the Validity of Uploaded Data

Loading data from external sources is kinda like inviting your crew for a job. You wanna make sure everyone shows up with the right attitude—or, in our case, in the right format. Even a tiny mistake in the uploaded file can lead to hours of debugging, wrong query results, or just mess up your table data.

Sometimes your file might sneak in empty lines, extra spaces, duplicates, or, say, text where you expect a number. And if the encoding is off, the table might just refuse to accept the file at all.

To avoid this, it's super important to check your data for validity—either before loading or right after. Let's break down how to do that.

Checking Data Structure

1. Comparing the table structure with the uploaded data

The very first step is to make sure your data matches your table's structure. For example, you created a students table to store info about students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    email VARCHAR(100) UNIQUE
);

If you loaded data into this table, let's just take a look at what's inside:

SELECT * FROM students;

The returned rows will show you all the records in the table. If the data structure in your CSV file doesn't match the table, you'll see errors during the load. But even if there were no errors, it doesn't mean your data is perfect.

2. Checking data types

Use PostgreSQL functions to check what's inside your columns. For example:

Checking for empty values (NULL):

If your table has required fields (NOT NULL), you gotta make sure they're actually filled in. For example:

SELECT * FROM students WHERE first_name IS NULL OR last_name IS NULL;

Checking data formats:

Sometimes data gets loaded as strings when it should be dates or numbers. To check this, use the right PostgreSQL functions, like:

SELECT * FROM students WHERE birth_date::DATE IS NULL;

This query will show rows where the birth_date field can't be cast to DATE.

Checking for Errors

1. Finding duplicates

Duplicate records are one of the most common problems. Let's say your data should be unique by email address (email). To check for duplicates, use this query:

SELECT email, COUNT(*)
FROM students
GROUP BY email
HAVING COUNT(*) > 1;

This query will show you all repeated email values and how many times they appear. If your email column is set as UNIQUE, loading such data will throw an error.

2. Checking for invalid data

If you expect the birth_date field to only have birth dates, you need to make sure all values are in a valid range. For example:

SELECT * FROM students
WHERE birth_date < '1900-01-01' OR birth_date > CURRENT_DATE;

This query will show rows where the birth date is way off from reality.

Dealing with Invalid Data

Once you've found issues, you gotta fix them. Let's see how you can do that.

1. Deleting invalid data

If you find rows in your table with empty names, you can delete them:

DELETE FROM students
WHERE first_name IS NULL OR last_name IS NULL;

But be careful with deleting data! It might be important, so maybe it's better to update it instead of deleting.

2. Updating data

If you find rows with missing data, you can update them based on other sources or make an educated guess. For example:

UPDATE students
SET email = 'unknown@example.com'
WHERE email IS NULL;

Visualizing Data for Analysis

1. Using aggregate functions

Sometimes it's helpful to count aggregates to check your data. For example, to see how many students were born each year, run:

SELECT EXTRACT(YEAR FROM birth_date) AS year, COUNT(*)
FROM students
GROUP BY year
ORDER BY year;

This query will show you the distribution by year and might point out anomalies (like if a suspiciously large group of students appeared in one year).

2. Checking data with constraints

Make sure your data matches the constraints set in your table, for example:

Checking for uniqueness:

SELECT DISTINCT email
FROM students;

If the number of unique values is less than the total number of rows—you've got duplicates.

Checking value ranges:

SELECT * FROM students
WHERE LENGTH(first_name) > 50 OR LENGTH(last_name) > 50;

This helps make sure student names don't go over the 50 character limit.

What if Everything's a Mess?

Sometimes the data is so bad, it's easier to just reload it from scratch.

1. Delete all rows from the table:

   TRUNCATE TABLE students;
   

2. Fix the original CSV file using Python, Excel, or whatever tool you like.

3. Reload the data using the COPY command.

Practical Use

Data validation skills will come in handy every time you work with external sources. In interviews, for example, you might be asked to write an SQL query to check the quality of incoming data—totally normal. In real projects, it's no easier: data from clients or other departments almost always comes with mistakes, and you'll be the one to spot them first and fix everything before it turns into bugs.

Regular data checks help keep your database tidy—and that's not just a formality, it's a real time, nerves, and team effort saver. So if you can quickly tell if your data's in order, you're already one step closer to being a PostgreSQL pro.