How to alter column to VARCHAR type with length L where L is based on the column aggregation like MAX(LENGTH(column)), PostgreSQL DB?

Question

I am trying to alter column named names type from text to VARCHAR with length L. Where L is the maximum length of the names. I am trying with inner/sub-query as follows:

Is this even possible? Any alternative to do this in a single query?

ALTER TABLE friends
ALTER COLUMN names TYPE VARCHAR(
    SELECT MAX(LENGTH(names))
    FROM friends
)

I get the following error in PgAdmin4 SQL console:

ERROR:  syntax error at or near "SELECT"
LINE 3:  SELECT MAX(LENGTH(card_number))

Answer 1

You can use the equivalent of VARCHAR(MAX) on Postgres, which can be achieved by simply using VARCHAR alone with no size:

ALTER TABLE friends ALTER COLUMN names TYPE VARCHAR;

Note that with VARCHAR any given string will only use storage for that string alone, with no padding (unlike CHAR).

Or, we could use the TEXT type:

ALTER TABLE friends ALTER COLUMN names TYPE TEXT;

Answer 2

You'll probably be better off using text. Answering the question: you can't use anything but integer constants in that context, so to achieve that "in a single query", you'll need dynamic SQL.

create table a(b varchar(60));
insert into a values ('123456789'),('12345');

select data_type,character_maximum_length 
from information_schema.columns 
where table_name='a' and table_schema='public' and column_name='b';

data_type	character_maximum_length
character varying	60

do $f$
declare new_len_limit int;
begin
select max(length(b)) from a into new_len_limit;
execute format('alter table a alter column b type varchar(%s)',new_len_limit);
end $f$ language plpgsql;

select data_type,character_maximum_length 
from information_schema.columns 
where table_name='a' and table_schema='public' and column_name='b';

data_type	character_maximum_length
character varying	9

alter table a alter column b type varchar(select 5);

ERROR:  syntax error at or near "select"
LINE 1: alter table a alter column b type varchar(select 5);
                                                  ^

create function max_b_len() returns int language sql as 'select 5';
alter table a alter column b type varchar(max_b_len());

ERROR:  syntax error at or near "max_b_len"
LINE 1: alter table a alter column b type varchar(max_b_len());
                                                  ^

fiddle