ALTER COLUMN token TYPE varchar('800');-CodePudding

How to convert that '800'(String) to 800(Integer)

I had use

ALTER COLUMN token TYPE varchar('800'::integer)

ALTER COLUMN token TYPE varchar(CAST('800' as integer))

but still not working

Please help, thanks

CodePudding user response：

A couple of suggestions based on:

create table t (fld_1 varchar);
 \d t
                      Table "public.t"
 Column |       Type        | Collation | Nullable | Default 
-------- ------------------- ----------- ---------- ---------
 fld_1  | character varying |           |          |

In psql:

\set type_length '10'
alter table t alter COLUMN fld_1 type varchar(:type_length);
ALTER TABLE

\d t
                        Table "public.t"
 Column |         Type          | Collation | Nullable | Default 
-------- ----------------------- ----------- ---------- ---------
 fld_1  | character varying(10) |           |          |

Using plpgsql:

CREATE OR REPLACE FUNCTION public.type_change(type_length character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    execute format('alter table t alter COLUMN fld_1 type varchar(%s)', type_length);
RETURN;
END;
$function$

select type_change('50');
 type_change 
-------------
 
 \d t
                        Table "public.t"
 Column |         Type          | Collation | Nullable | Default 
-------- ----------------------- ----------- ---------- ---------
 fld_1  | character varying(50) |           |          |

You could expand the above to take the table and column names to make it more generic.

Of course another option is to build the query dynamically in whatever client/language you are using to interact with the database.