Union tables with same prefix in postgresql

Question

I have number of tables with prefix "tb_" in a schema (not public) in postgres database. I want to create a new table as union of all these tables with prefix "tb_". All the tables have same structure and no duplicates. I could do it manually using the SQL statement as below .

CREATE TABLE schema1.tb
AS
SELECT *
FROM schema1.tb_1
UNION
SELECT *
FROM schema1.tb_2
UNION
SELECT *
FROM schema.tb_3

However, I would like to automate as there are many tables. Perhaps its possible with PL/pgSQL. I have no knowledge of writing PLSQL code hence asking for help here.

Also after creating the new table I would like to drop all the tables with prefix "tb_".

Below is my attempt based on the answer by spatialhast

CREATE OR REPLACE FUNCTION maskunion(IN _schema TEXT, IN _parttionbase TEXT, TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || $3;
    EXECUTE 'CREATE TABLE ' || $3 || '
        (
          col1 double precision,
          col2 double precision,
          col3 double precision,
          col4 double precision,
          col5 double precision
        )';

    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'INSERT INTO ' || $3 || ' SELECT * FROM ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;


SELECT maskunion('schema1', 'tb_', 'schema1.new_table');

There is an answer very close to your question [Here](https://dba.stackexchange.com/q/204425/42478). You will just need to change the result script to run one create statement instead of multiple with multiple inserts. See it if helps. And if you still are not able to, post your attempt here. — Jorge Campos, Mar 15 '22 at 21:57
I am afraid but I do not understand everything in the code you referenced. I found another related solution which I have modified and posted. It seems to work well. Thanks — Jio, Mar 16 '22 at 13:27
No worries, if you found an answer for it you should add it as an answer to your own question. So in the future it may help others :) — Jorge Campos, Mar 16 '22 at 17:24

score 0 · Accepted Answer · answered Mar 19 '22 at 18:44

Below is one of the solution based on the answer by spatialhast.

CREATE OR REPLACE FUNCTION maskunion(IN _schema TEXT, IN _parttionbase TEXT, TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || $3;
    EXECUTE 'CREATE TABLE ' || $3 || '
        (
          col1 double precision,
          col2 double precision,
          col3 double precision,
          col4 double precision,
          col5 double precision
        )';

    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'INSERT INTO ' || $3 || ' SELECT * FROM ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;


SELECT maskunion('schema1', 'tb_', 'schema1.new_table');

Union tables with same prefix in postgresql

1 Answers1