How do I create a column in PgAdmin4 that is based of another column-CodePudding

I want to create a column that gives specific values for words in the previous column. Example if column 1 says 'cars' I want column 2 to say 1. And if column 1 says 'trains' I want column 2 to say 2. But if column 1 doesn't say cars or trains I want column 2 to return a 0.

CodePudding user response：

ALTER TABLE mytable ADD COLUMN col2 VARCHAR(100);

UPDATE mytable SET col2 = CASE col1 WHEN 'cars' THEN 1 WHEN 'trains' THEN 2 ELSE 0 END;

CodePudding user response：

You could create the new column and then use a CASE expression to populate it with the values you want:

UPDATE yourTable
SET new_col = CASE old_col WHEN 'cars' THEN 1
                           WHEN 'trains' THEN 2
                           ELSE 0 END;

In case the column new_col does not already exist, then create it first:

ALTER TABLE yourTable ADD COLUMN new_col VARCHAR(100);