INSERT INTO with the CASE function

Question

so I have this table I'm working with and I created an additional column (column B) to distinct some values. Column A are integers and I am trying to use the CASE function to identify their respective names and then insert them into Column B. I thought I had the code right but when I query it claims to be successful but returns just null values in Column B. Where am I going wrong?

This is my current code:

INSERT INTO astro(star_name)
SELECT 
CASE star_type
    WHEN 0 THEN 'Brown'
    WHEN 1 THEN 'Red_Dwarf'
    WHEN 2 THEN 'White_Dwarf'
    WHEN 3 THEN 'Main_Sequence'
    WHEN 4 THEN 'Supergiant'
    WHEN 5 THEN 'Hellagiant'
    ELSE 'Basic'
END
FROM astro

I expected for the star_name to return the corresponding names, but instead I just got a whole bunch of wack ahh nulls :/ I appreciate your help community, thanks!

Answer 1

You should use UPDATE to change existing rows, not INSERT

More examples here:

• PostgreSQL UPDATE
• PostgreSQL INSERT

UPDATE astro
SET star_name = CASE star_type
    WHEN 0 THEN 'Brown'
    WHEN 1 THEN 'Red_Dwarf'
    WHEN 2 THEN 'White_Dwarf'
    WHEN 3 THEN 'Main_Sequence'
    WHEN 4 THEN 'Supergiant'
    WHEN 5 THEN 'Hellagiant'
    ELSE 'Basic'
END

see: DBFIDDLE