Обсуждение: Expanding a VARCHAR on a large table that has inherited subtables ...
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
The main table, which has no data in it, has a constraint:
This same constraint is on the subtables since they are created with:
My question is:
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
I receive aALTER TABLE <tablename> ALTER COLUMN <columnname> TYPE VARCHAR(16);
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:
- Should I create a PL/PGSQL script to:
- go to each sub-table
- drop constraint from sub-table
- remove inheritance of sub-table
- alter column on sub-table
- add constraint again to sub-table
- drop constraint from main table
- alter column on main table
- add constraint to main table
- go to each of previous sub-tables
- set inheritance as before of sub-table to main table
- go to each sub-table
- drop the constraint on table and subtables, apply the alter table alter column and add the constraint again
- any other way?
Best regards,
Paulo Correia