Good evening!

In a loop, using pg_class, created temporary tablets that I saved to the data before updating in case a rollback is required. In several tables it is empty because there was no related data. And now you need to leave only signs containing information. Please tell me how to remove only empty tables from the schema with a name starting with tmp_123 ...?

Thank!

  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

The easiest option is to create a function that:

  1. Selects all the names of the matching tables;
  2. Checks how many entries are in the table;
  3. Deletes empty.

For example:

CREATE OR REPLACE FUNCTION remove_tmp_123() RETURNS integer AS $BODY$ DECLARE check_table varchar; count_rows integer; removed_tables integer; BEGIN removed_tables := 0; FOR check_table IN SELECT table_name FROM information_schema.tables where 1=1 and table_catalog = 'you_db_name' and table_type = 'BASE TABLE' and table_name ilike 'tmp_123%' LOOP execute format('SELECT COUNT(*) from %I ;', check_table) into count_rows; IF (count_rows = 0) then execute format('DROP TABLE %I ;', check_table); removed_tables := removed_tables + 1; end IF; END LOOP; RETURN removed_tables; END; $BODY$ LANGUAGE plpgsql;

Check:

 # create table tmp_123 (name varchar); CREATE TABLE Time: 99,390 ms # create table tmp_123534 (name varchar); CREATE TABLE Time: 120,950 ms # create table tmp_12325t343 (name varchar); CREATE TABLE Time: 62,786 ms # insert into tmp_123534(name) values ('1234546'); INSERT 0 1 Time: 21,371 ms =# select remove_tmp_123(); remove_tmp_123 ---------------- 2 (1 row) # SELECT table_name FROM information_schema.tables where 1=1 and table_catalog = 'you_db_name' and table_type = 'BASE TABLE' and table_name ilike 'tmp_123%' ; table_name ------------ tmp_123534 (1 row)