Expanding a VARCHAR on a large table that has inherited subtables ...

[Paulo Correia <paulo.correia@xxxxxxxxx>]

Hello,

I have a table that has several inherited sub-tables with a partition constraint restriction. All the sub-tables have the same structure and they are used to distribute load, each sub-table has typically more than 6million records.

When I try to issue a

ALTER TABLE <tablename> ALTER COLUMN <columnname> TYPE VARCHAR(16);

I receive a

ERROR:  constraint must be added to child tables too

The main table, which has no data in it, has a constraint:

"<constraintname>" CHECK (<columnname> IS NULL OR "substring"(<columnname>::text, '^[a-zA-Z0-9_,. -]*$'::text) IS NOT NULL AND "substring"(

<columnname>::text, '^[a-zA-Z0-9_,. -]*$'::text) = <columnname>::text)

This same constraint is on the subtables since they are created with:

CREATE TABLE <subtablename> (LIKE <tablename> INCLUDING ALL);

ALTER TABLE <subtablename> INHERIT <tablename>;

My question is:

1. Should I create a PL/PGSQL script to:
1. go to each sub-table
1. drop constraint from sub-table
2. remove inheritance of sub-table
   
3. alter column on sub-table
4. add constraint again to sub-table
   
2. drop constraint from main table
3. alter column on main table
4. add constraint to main table
5. go to each of previous sub-tables
1. set inheritance as before of sub-table to main table
2. drop the constraint on table and subtables, apply the alter table alter column and add the constraint again
3. any other way?

Best regards,
Paulo Correia