How to add new column using CAST and info from another table in SQL?

Question

I'm trying to understand the relationship between two operations in SQL - ADD COLUMN and CAST(). I tried to create a new column containing the lengths of another column's values, while that other column is inconveniently of type INTEGER:

ALTER TABLE inventory 
    ADD inventory_id_len AS (CHAR_LENGTH(CAST(inventory_id AS VARCHAR)) FROM rental);

But it returns:

ERROR:  syntax error at or near "AS"
LINE 4:  ADD inventory_id_len AS (CHAR_LENGTH(CAST(inventory_id AS V...

Thanks.

Answer 1

In Postgres, you need to use the generated always ... stored syntax to add a computed column. For your use case, that would look like:

alter table inventory 
    add inventory_id_len int 
    generated always as (char_length(inventory_id::text)) stored
;

A subquery makes no sense in that context; the computed column takes the value of column inventory_id on the very same row.