Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 16.6 (Debian 16.6-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

CREATE TABLE astro(star_type INT);

INSERT INTO astro VALUES
  (1),(2),(3),(0),(4),(5),(6);

CREATE TABLE

INSERT 0 7

SELECT * FROM astro;

star_type

1

2

3

0

4

5

6

SELECT 7

ALTER TABLE astro ADD COLUMN star_name VARCHAR(20);

ALTER TABLE

SELECT * FROM astro;

star_type
star_name

1
null

2
null

3
null

0
null

4
null

5
null

6
null

SELECT 7

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

INSERT 0 7

SELECT * FROM astro;

star_type
star_name

1
null

2
null

3
null

0
null

4
null

5
null

6
null

null
Red_Dwarf

null
White_Dwarf

null
Main_Sequence

null
Brown

null
Supergiant

null
Hellagiant

null
Basic

SELECT 14

-- remove those null values
DELETE FROM astro WHERE star_type is null;

SELECT * FROM astro;

DELETE 7

star_type
star_name

1
null

2
null

3
null

0
null

4
null

5
null

6
null

SELECT 7

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

UPDATE 7

SELECT * FROM astro;

star_type
star_name

1
Red_Dwarf

2
White_Dwarf

3
Main_Sequence

0
Brown

4
Supergiant

5
Hellagiant

6
Basic

SELECT 7

star_type	star_name
1	null
2	null
3	null
0	null
4	null
5	null
6	null

star_type	star_name
1	null
2	null
3	null
0	null
4	null
5	null
6	null
null	Red_Dwarf
null	White_Dwarf
null	Main_Sequence
null	Brown
null	Supergiant
null	Hellagiant
null	Basic

star_type	star_name
1	null
2	null
3	null
0	null
4	null
5	null
6	null