UPDATE TABLE where column IS NULL isn't working

I’m on the PostgreSQL project called " Building an Inventory Database with PostgreSQL".
For step 3, the instructions say…

" The parts table is missing values in the description column. Alter the table so that all rows have a value for description` ."

After trying on my own AND copying and pasting the Hint which was…

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

…my table still isn’t replacing null values with “None Available”. Just to be safe I made sure that they weren’t all just blank spaces so I also tried…

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

This didn’t work either. Is my syntax wrong or is this a bug?

I think you’re on the right lines but there’s actually a single whitespace in the “empty” rows. You can catch it if you visually select/highlight it (how I found it). Try using ' ' instead.

2 Likes

Still does not work! it’s quite annoying that I can’t do this!

You’ll need to provide a little more information for anyone who’s willing to assist; it’s not clear what you’ve tried and what you expected to happen.

I have the same problem. and your suggestion did not work

Could you post your code please?

here are my code and screenshot of the results, as you can see the null values are not filled!

SELECT * FROM parts
LIMIT 10;

ALTER TABLE parts
ALTER COLUMN code SET NOT NULL;
 
ALTER TABLE parts
ADD UNIQUE(code);

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

SELECT * FROM parts;

Here with empty string:

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

With one space as string:

SELECT * FROM parts
LIMIT 10;

ALTER TABLE parts
ALTER COLUMN code SET NOT NULL;
 
ALTER TABLE parts
ADD UNIQUE(code);

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

SELECT * FROM parts;

In this case, those empty ones disappear

Disappear or just move relative to where you’re looking? View more of the list or if it’s too long look just query rows that match your new description.

1 Like

this actually worked for me, they just moved to the bottom of the list with updated descriptions :slight_smile:

thanks for the tip, I was so confused why this wasn’t working with NULL… surely this is a bug in the exercise??