Backfilling Problem - Building an Inventory Database with PostgreSQL

My query isn’t coming back as backfilling the NULL spaces in description. I assume the value s/b NULL. My script looks correct, why isn’t it working? Bug??

ALTER TABLE parts
ALTER COLUMN code SET NOT NULL;

ALTER TABLE parts
ADD UNIQUE(code);

UPDATE parts
SET description = 'None Available'
WHERE description IS NULL;

ALTER TABLE parts
ALTER COLUMN description SET NOT NULL;

SELECT * FROM parts limit 10;

I’m expecting the blank cells on line 3 and 9 to be filled with ‘None Available’.

https://www.codecademy.com/paths/back-end-engineer-career-path/tracks/becp-sql-for-back-end-development/modules/fscp-sql-creating-updating-and-deleting-data/projects/constraints-project

1 Like

I remember running into this when I completed the project too. After investigating, I found that it wasn’t exactly a bug, but was closer to misleading data.

What’s happening is the “blank” descriptions are actually single spaces, so they aren’t NULL. The constraint added to the description column is being enforced properly, which you’ll be able to test as you continue the project. You could alter your UPDATE query to change the value of description if it’s currently equal to a space as well if you’d like.

Also, it’s worth noting that almost all the columns that have text values (if not all) have a leading space even if they don’t appear to be blank. Depending on how you handle Step #15, this could be problematic too. If you look at the CSV files they use to seed the database, you’ll see the extra spaces after the commas.

2 Likes

Thank you for your reply. I tried this as well and it throws an error. Really slows down my progress when these things aren’t working. Thumbs down Codecademy.

UPDATE parts
SET description = 'None Available'
WHERE description IS ' ';

It’s throwing an error because that’s not valid SQL. You can use = to compare it to a regular string. You needed to use IS NULL because of NULL

Click here if you want the SQL

Since this isn’t part of the project instructions and you already wrote it the way they intended, here is a working alternative:

UPDATE parts
SET description = 'Need Description'
WHERE description IS NULL
  OR description = ' ';
2 Likes

Awesome! Yeah, that wasn’t in our material

Thank you!