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.

A subquery is not allowed for a generated column. Sample data, desired results, and an explanation of what you want to do would help. — Gordon Linoff, Nov 12 '20 at 11:56
Yes definitely. Even though there are already some answers, I will add what the desired results are — Friedman, Nov 12 '20 at 12:27

score 1 · Answer 1 · answered Nov 12 '20 at 12:00

If you want to add the length of the id as a generated column:

ALTER TABLE inventory 
    ADD inventory_id_len INT GENERATED ALWAYS AS (LEN(inventory_id::text) STORED;

Because Postgres does not (yet) support virtual generated columns, a view might be more in line with what you want:

create view v_inventory as
    select i.*, len(inventory_id::text) as inventory_id_len
    from inventory i;

score 1 · Accepted Answer · edited Nov 12 '20 at 12:19

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.

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

2 Answers2