CODEWITHSUNDEEP
postgresql
Lucien S.
Lucien S.

Reputation: 5345

Selecting all records in all tables of the Public Schema in PostgreSQL

I have several tables in my PostgreSQL database's Public Schema. The tables are named "projects_2019", "projects_2020", "projects_2021", etc. and have the same columns. The idea is that a new table will be added every year.

I would like to select all records in all of the tables whose name includes "projects_", how could I do this without naming each and every table name (since I don't know how many there will be in the future)?

Here's what I have so far:

WITH t as
    (SELECT * FROM information_schema.tables WHERE table_schema = 'public' and table_name ~ 'projects_')
SELECT * FROM t

Upvotes: 1

Views: 959

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try like this:

FOR i IN SELECT table_name 
           FROM information_schema.tables 
          WHERE table_schema = 'public' 
          and table_name ~ 'projects_'

LOOP
    sqlstr := sqlstr || format($$

        UNION
        SELECT name FROM %I
    $$,
    i.table_name);
END LOOP;

EXECUTE sqlstr;

Upvotes: 0

Ramin Faracov
Ramin Faracov

Reputation: 3303

You can do it using dynamic SQL and information_schema. For Example:

-- Sample Data
CREATE TABLE table1 (
    id int4 NULL,
    caption text NULL
); 

CREATE TABLE table2 (
    id int4 NULL,
    caption text NULL
); 

CREATE TABLE table3 (
    id int4 NULL,
    caption text NULL
); 

CREATE TABLE table4 (
    id int4 NULL,
    caption text NULL
); 

INSERT INTO table1 (id, caption) VALUES (1, 'text1');
INSERT INTO table2 (id, caption) VALUES (2, 'text2');
INSERT INTO table3 (id, caption) VALUES (3, 'text3');
INSERT INTO table4 (id, caption) VALUES (4, 'text4');

-- create function sample: 

CREATE OR REPLACE FUNCTION select_tables()
 RETURNS table(id integer, caption text)
 LANGUAGE plpgsql
AS $function$
declare 
    v_sql text;
    v_union text;
begin
    
    SELECT string_agg('select * from ' || table_schema || '.' || table_name, ' union all ')
    into v_sql
    FROM information_schema.tables WHERE table_schema = 'public' and table_name ~ 'table';

    return query 
    execute v_sql; 
    
end ;
$function$
;

-- selecting data: 
select * from select_tables()

-- Result: 
id  caption
1   text1
2   text2
3   text3
4   text4

Upvotes: 1

Related Questions