Question 285415 in PostgreSQL Question posted in
The official documentation can be found here.

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

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))
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search