CODEWITHSUNDEEP
postgresqltypesdynamic-sqlddl
whoknows
whoknows

Reputation: 306

PostgreSQL increase column length

Example I have table named from A - Z , but only

    table "A" and table "J"

have a 

    column clm varchar(10).

But then I realized that I needed clm to be of size 50 (given that I do not know that A and J have a column clm).

Is there script/query in PG that can do this thing?

Upvotes: 5

Views: 17934

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658302

Just use text or varchar, not varchar(n). If you really need to restrict a column to a maximum length use a CHECK constraint. Related answer:

Anyway, the basic statement is:

ALTER TABLE tbl ALTER clm TYPE varchar(50); -- or rather: text

You don't need a USING clause with explicit conversion instructions, as long as there an implicit a cast registered, which is the case for varchar(n) -> varchar(n) / text. Details:

Script based on system catalogs:

DO
$do$
DECLARE
    _sql text;
BEGIN
   FOR _sql IN 
      SELECT format('ALTER TABLE %s ALTER %I TYPE varchar(50)'
                   , attrelid::regclass
                   , a.attname)
      FROM   pg_namespace n
      JOIN   pg_class     c ON c.relnamespace = n.oid
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  n.nspname = 'public'  -- your schema
      AND    a.attname = 'clm'     -- column name (case sensitive!)
      AND    a.attnum > 0
      AND    NOT a.attisdropped
   LOOP
      RAISE NOTICE '%', _sql;  -- debug before you execute
      -- EXECUTE _sql;
   END LOOP;
END
$do$;

Upvotes: 10

Stefan Sprenger
Stefan Sprenger

Reputation: 1080

Not validated just "brain tested"

DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'clm' AND TABLE_SCHEMA = 'public'  
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || 'ALTER COLUMN clm TYPE varchar(50);';
    END LOOP;
END;
$$;

Upvotes: 0

Sathish
Sathish

Reputation: 4487

Create a procedure 

CREATE OR REPLACE FUNCTION fn_sizeupdate()
 RETURNS Void AS
 $BODY$ 
 DECLARE 
 query text;

 BEGIN 

for query in 

select 'alter table '|| table_name ||' alter clm type varchar(50) 
USING clm ::varchar(50);'
from information_schema.columns where table_schema = 'public' and
column_name='name';

loop

execute query

 End loop;

END; 
$BODY$
LANGUAGE plpgsql VOLATILE

Upvotes: 1

Related Questions