PostgreSQL increase column length

Question

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?

do you want increase the size of clm, in which tables are have that column? right — Sathish, Aug 13 '14 at 08:53
SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'clm' to get all tables with col "clm" — Stefan Sprenger, Aug 13 '14 at 08:54
@Sathish yes. Something like 'increase size where column_name = "clm"', which applies to tables A - Z. — whoknows, Aug 13 '14 at 08:55
@StefanSprenger This is a good start, I tried your query in MySQL and it works. I'll to work on the UPDATE operation. — whoknows, Aug 13 '14 at 09:00
@whoknows i am also looking for some kind of alter table iteration — Stefan Sprenger, Aug 13 '14 at 09:01
@whoknows try this DO $$ DECLARE row record; BEGIN FOR row IN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'clm' LOOP EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || 'ALTER COLUMN clm TYPE varchar(50);'; END LOOP; END; $$; — Stefan Sprenger, Aug 13 '14 at 09:18
Double quotes are for identifiers, single quotes for data. I fixed your question. — Erwin Brandstetter, Aug 13 '14 at 22:14

score 9 · Accepted Answer · edited May 23 '17 at 11:58

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:

Change PostgreSQL columns used in views

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:

Generate series of dates - using date type as input

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$;

Sathish · Answer 2 · 2014-08-13T11:04:59.473

1

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

edited Aug 13 '14 at 11:04

answered Aug 13 '14 at 09:21

Sathish

4,419
4
30
59

this is PostgreSQL syntax right? does this apply to INGRES. I can't test this coz' I don't have the environment. sorry. – whoknows Aug 13 '14 at 10:17
@whoknows yes it `postgresql` syntax – Sathish Aug 13 '14 at 10:29
@Sathish You can use `RETURNS void` instead of `RETURNS text` if you do not want to return anything. – Ihor Romanchenko Aug 13 '14 at 10:39

Stefan Sprenger · Answer 3 · 2014-08-13T14:36:24.727

0

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;
$$;

edited Aug 13 '14 at 14:36

answered Aug 13 '14 at 09:24

Stefan Sprenger

1,050
20
42

You need to include a `where table_schema = 'public'` into the select if you hardcode the schema in the `alter table` statement. And using `like` without a wildcard doesn't really make sense. – Aug 13 '14 at 09:53

PostgreSQL increase column length

3 Answers3