How to make lots of tables have the same columns as a template?

In PostgreSQL I have tables to store many millions of rows, with over 90 columns. I have under 100 of these, but when I add or adjust a column in the template “d_template” table, I have to manually change them all-and they are growing. The working tables all have a prefix of “d_”, which has it’s own challenges, as the underscore character “_” is seen on some platforms as a variable/wildcard. Add to this that SQL treats a “_” differently than pl/pgsql… I’m using JDBC/Postgresql.

I wanted to run a single function with the template table hard-coded, and have it do the following:

</code><br /><br /><code>CREATE OR REPLACE FUNCTION raw.f_update_raw_d_template_with_data_source() RETURNS VOID LANGUAGE plpgsql AS $$</code><br /><code>DECLARE</code><br /><code>source_schema CONSTANT text := 'data';</code><br /><code>source_table CONSTANT text := 'd_template';</code><br /><code>target_schema CONSTANT text := 'raw';</code><br /><code>target_prefix CONSTANT text := 'd_';</code><br /><code>source_columns RECORD;</code><br /><code>target_column RECORD;</code><br /><code>column_type text;</code><br /><br /><br /><code>BEGIN</code><br /><code>-- Loop through each column in the source template table</code><br /><code>FOR source_columns IN</code><br /><code>SELECT column_name, data_type, is_nullable, character_maximum_length</code><br /><code>FROM information_schema.columns</code><br /><code>WHERE table_schema = source_schema</code><br /><code>AND table_name = source_table</code><br /><code>LOOP</code><br /><code>-- Loop through each target table that starts with the specified prefix</code><br /><code>FOR target_column IN</code><br /><code>SELECT column_name, data_type, is_nullable</code><br /><code>FROM information_schema.columns</code><br /><code>WHERE table_schema = target_schema</code><br /><code>AND table_name LIKE target_prefix || '%' ESCAPE '\'</code><br /><code>AND column_name = source_columns.column_name</code><br /><code>LOOP</code><br /><code>-- If column exists, check if it matches the type and nullable constraints</code><br /><code>IF target_column.data_type != source_columns.data_type OR </code><br /><code>(target_column.is_nullable = 'YES' AND source_columns.is_nullable = 'NO') THEN</code><br /><br /><code>column_type := source_columns.data_type;</code><br /><br /><code></code><br /><br /><code>-- Adjust character length if applicable</code><br /><code>IF source_columns.character_maximum_length IS NOT NULL THEN</code><br /><code>column_type := column_type || '(' || source_columns.character_maximum_length || ')';</code><br /><code>END IF;</code><br /><br /><code></code><br /><br /><code>EXECUTE format(</code><br /><code>'ALTER TABLE %I.%I ALTER COLUMN %I TYPE %s',</code><br /><code>target_schema,</code><br /><code>target_prefix || '%',</code><br /><code>source_columns.column_name,</code><br /><code>column_type</code><br /><code>);</code><br /><br /><code></code><br /><br /><code>-- Adjust nullable constraint</code><br /><code>IF source_columns.is_nullable = 'NO' THEN</code><br /><code>EXECUTE format(</code><br /><code>'ALTER TABLE %I.%I ALTER COLUMN %I SET NOT NULL',</code><br /><code>target_schema,</code><br /><code>target_prefix || '%',</code><br /><code>source_columns.column_name</code><br /><code>);</code><br /><code>ELSE</code><br /><code>EXECUTE format(</code><br /><code>'ALTER TABLE %I.%I ALTER COLUMN %I DROP NOT NULL',</code><br /><code>target_schema,</code><br /><code>target_prefix || '%',</code><br /><code>source_columns.column_name</code><br /><code>);</code><br /><code>END IF;</code><br /><br /><code></code><br /><br /><code>END IF;</code><br /><code>END LOOP;</code><br /><br /><code></code><br /><br /><code>-- If column does not exist in the target table, add it</code><br /><code>IF NOT FOUND THEN</code><br /><code>column_type := source_columns.data_type;</code><br /><br /><code></code><br /><br /><code>-- If character_maximum_length is specified, add that to column_type</code><br /><code>IF source_columns.character_maximum_length IS NOT NULL THEN</code><br /><code>column_type := column_type || '(' || source_columns.character_maximum_length || ')';</code><br /><code>END IF;</code><br /><br /><code></code><br /><br /><code>EXECUTE format(</code><br /><code>'ALTER TABLE %I.%I ADD COLUMN %I %s %s',</code><br /><code>target_schema,</code><br /><code>target_prefix || '%',</code><br /><code>source_columns.column_name,</code><br /><code>column_type,</code><br /><code>CASE WHEN source_columns.is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END</code><br /><code>);</code><br /><code>END IF;</code><br /><code>END LOOP;</code><br /><br /><code></code><br /><br /><code>-- Remove any columns from the target table that are not in the source table</code><br /><code>FOR target_column IN</code><br /><code>SELECT column_name</code><br /><code>FROM information_schema.columns</code><br /><code>WHERE table_schema = target_schema</code><br /><code>AND table_name LIKE target_prefix || '%' ESCAPE '\'</code><br /><code>AND column_name NOT IN (</code><br /><code>SELECT column_name</code><br /><code>FROM information_schema.columns</code><br /><code>WHERE table_schema = source_schema</code><br /><code>AND table_name = source_table</code><br /><code>)</code><br /><code>LOOP</code><br /><code>EXECUTE format(</code><br /><code>'ALTER TABLE %I.%I DROP COLUMN %I',</code><br /><code>target_schema,</code><br /><code>target_prefix || '%',</code><br /><code>target_column.column_name</code><br /><code>);</code><br /><code>END LOOP;</code><br /><br /><code>END $$;

</code> <code>CREATE OR REPLACE FUNCTION raw.f_update_raw_d_template_with_data_source() RETURNS VOID LANGUAGE plpgsql AS $$</code> <code>DECLARE</code> <code>source_schema CONSTANT text := 'data';</code> <code>source_table CONSTANT text := 'd_template';</code> <code>target_schema CONSTANT text := 'raw';</code> <code>target_prefix CONSTANT text := 'd_';</code> <code>source_columns RECORD;</code> <code>target_column RECORD;</code> <code>column_type text;</code> <code>BEGIN</code> <code>-- Loop through each column in the source template table</code> <code>FOR source_columns IN</code> <code>SELECT column_name, data_type, is_nullable, character_maximum_length</code> <code>FROM information_schema.columns</code> <code>WHERE table_schema = source_schema</code> <code>AND table_name = source_table</code> <code>LOOP</code> <code>-- Loop through each target table that starts with the specified prefix</code> <code>FOR target_column IN</code> <code>SELECT column_name, data_type, is_nullable</code> <code>FROM information_schema.columns</code> <code>WHERE table_schema = target_schema</code> <code>AND table_name LIKE target_prefix || '%' ESCAPE '\'</code> <code>AND column_name = source_columns.column_name</code> <code>LOOP</code> <code>-- If column exists, check if it matches the type and nullable constraints</code> <code>IF target_column.data_type != source_columns.data_type OR </code> <code>(target_column.is_nullable = 'YES' AND source_columns.is_nullable = 'NO') THEN</code> <code>column_type := source_columns.data_type;</code> <code></code> <code>-- Adjust character length if applicable</code> <code>IF source_columns.character_maximum_length IS NOT NULL THEN</code> <code>column_type := column_type || '(' || source_columns.character_maximum_length || ')';</code> <code>END IF;</code> <code></code> <code>EXECUTE format(</code> <code>'ALTER TABLE %I.%I ALTER COLUMN %I TYPE %s',</code> <code>target_schema,</code> <code>target_prefix || '%',</code> <code>source_columns.column_name,</code> <code>column_type</code> <code>);</code> <code></code> <code>-- Adjust nullable constraint</code> <code>IF source_columns.is_nullable = 'NO' THEN</code> <code>EXECUTE format(</code> <code>'ALTER TABLE %I.%I ALTER COLUMN %I SET NOT NULL',</code> <code>target_schema,</code> <code>target_prefix || '%',</code> <code>source_columns.column_name</code> <code>);</code> <code>ELSE</code> <code>EXECUTE format(</code> <code>'ALTER TABLE %I.%I ALTER COLUMN %I DROP NOT NULL',</code> <code>target_schema,</code> <code>target_prefix || '%',</code> <code>source_columns.column_name</code> <code>);</code> <code>END IF;</code> <code></code> <code>END IF;</code> <code>END LOOP;</code> <code></code> <code>-- If column does not exist in the target table, add it</code> <code>IF NOT FOUND THEN</code> <code>column_type := source_columns.data_type;</code> <code></code> <code>-- If character_maximum_length is specified, add that to column_type</code> <code>IF source_columns.character_maximum_length IS NOT NULL THEN</code> <code>column_type := column_type || '(' || source_columns.character_maximum_length || ')';</code> <code>END IF;</code> <code></code> <code>EXECUTE format(</code> <code>'ALTER TABLE %I.%I ADD COLUMN %I %s %s',</code> <code>target_schema,</code> <code>target_prefix || '%',</code> <code>source_columns.column_name,</code> <code>column_type,</code> <code>CASE WHEN source_columns.is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END</code> <code>);</code> <code>END IF;</code> <code>END LOOP;</code> <code></code> <code>-- Remove any columns from the target table that are not in the source table</code> <code>FOR target_column IN</code> <code>SELECT column_name</code> <code>FROM information_schema.columns</code> <code>WHERE table_schema = target_schema</code> <code>AND table_name LIKE target_prefix || '%' ESCAPE '\'</code> <code>AND column_name NOT IN (</code> <code>SELECT column_name</code> <code>FROM information_schema.columns</code> <code>WHERE table_schema = source_schema</code> <code>AND table_name = source_table</code> <code>)</code> <code>LOOP</code> <code>EXECUTE format(</code> <code>'ALTER TABLE %I.%I DROP COLUMN %I',</code> <code>target_schema,</code> <code>target_prefix || '%',</code> <code>target_column.column_name</code> <code>);</code> <code>END LOOP;</code> <code>END $$;

The IT Guys Support Blog

How to make lots of tables have the same columns as a template?

Our Various Regional Facilities

Administrivia

Helpdesk Access

Click the shamrock to email us!

The Home Computer