Search code examples
postgresqlalter

how to change the length of varchar of array in postgresql database without losing data

I tried with the code below, but raised a "syntax error at or near array". Googled around, and nothing found. Is it possible to do so? Thanks!

alter table "tablename" alter column "columnname" TYPE ARRAY(VARCHAR(200));
Solution

  • It's unclear to me if you want to increase the length of each entry, or the length of the array.

    An array declaration follows the form datatype[] - the [] makes the column an array and the data type specification is the one for the base type.

    So, if you want to increase the length of each array element, just declare an array with a longer varchar length: varchar(200)[]:

    alter table "tablename" 
   alter column "columnname" TYPE varchar(200)[];

    If you want to use the ARRAY keyword, that needs to be put after the data type:

    alter table "tablename" 
   alter column "columnname" TYPE varchar(200) array;

    If you want to increase the length of the array (=allow more array elements) you don't need to do anything because even if you did specify an array dimension, this is not enforced by Postgres