Splitting up a few big tables into multiple ones and avoiding empty tables

by

The question:

I am in the process of restructurizing and unifying a large database in PostgreSQL. One part of this is to split up some large tables into smaller ones according to their value in ‘key’. The function I have written for this works well and looks like this:

CREATE OR REPLACE FUNCTION split_tables (table_string varchar(100)) RETURNS void AS
$$
DECLARE
r_row record;

BEGIN
FOR r_row IN SELECT type FROM type_list
LOOP
EXECUTE 'CREATE TABLE public.id_' || table_string || '__' || r_row.type || ' AS (SELECT * FROM id_'
|| table_string || ' WHERE lower(id_' || table_string || '.key) = lower(''' || r_row.type || '''));';
END LOOP;
END;
$$
LANGUAGE plpsql;

So, this goes through all entries in type_list and creates tables for each type. To do this for all tables that need to be split I call this function with
SELECT tobesplit.id FROM tobesplit, LATERAL split_tables(tobseplit.id);

However, this creates tables for ALL entries in type_list, even if there are no entries in the table to be split for some types. So in the end, I create a whole bunch of empty tables that I don’t need. I looked for ways to only create tables if the result is not empty, and found a possible solution in the answer for: CREATE TABLE IF ONLY NOT EMPTY RESULT SET.

This works if I test it seperately, but I have no idea how to incorporate this into the exectue statement. I always get syntax errors as soon as I write an IF or CASE-clause into the EXECUTE statement.

I am thankful for any ideas 🙂

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

You could change

FOR r_row IN SELECT type FROM type_list

to

FOR r_row IN
   EXECUTE format(
              'SELECT DISTINCT lower(key) AS type FROM %I',
              'id_' || table_string
           )

I would suggest that you use list partitioning and lump all the keys that don’t exist anywhere into a single partition.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment