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?

Intercostal

do you want increase the size of clm, in which tables are have that column? right – Keener

SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'clm' to get all tables with col "clm" – Halmahera

@Keener yes. Something like 'increase size where column_name = "clm"', which applies to tables A - Z. – Intercostal

@StefanSprenger This is a good start, I tried your query in MySQL and it works. I'll to work on the UPDATE operation. – Intercostal

@Intercostal i am also looking for some kind of alter table iteration – Halmahera

@Intercostal 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; $$; – Halmahera

Double quotes are for identifiers, single quotes for data. I fixed your question. – Drucilladrucy

Answer 1

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

Drucilladrucy

Answer 2

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

Keener

this is PostgreSQL syntax right? does this apply to INGRES. I can't test this coz' I don't have the environment. sorry. – Intercostal

@Intercostal yes it postgresql syntax – Keener

@Keener You can use RETURNS void instead of RETURNS text if you do not want to return anything. – Scenic

Answer 3

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

Halmahera

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. – Aldercy