I need to remove selected tables from the Postgres SQL. It would better to use like or where clause.
Like I have
TABLE_A
TABLE_B
TABLE_C
-
-
-
TABLE_N
I need to delete
TABLE_A to TABLE_X
Add a comment
|
2 Answers
Can be done with a single command, which is faster - in case this is a recurring task.
Add IF EXISTS if the existence of any tables is uncertain. This way we save an extra trip to the system catalogs (information_schema.tables or pg_catalog.pg_tables).
And you may want to add CASCADE:
DO
$do$
BEGIN
-- child safety device: quote RAISE instead of EXECUTE to prime the bomb
-- EXECUTE (
RAISE NOTICE '%', (
SELECT 'DROP TABLE IF EXISTS'
|| string_agg('table_' || chr(ascii('a') + g) , ', ')
|| ' CASCADE;'
FROM generate_series(0,13) g
);
END
$do$;
Generates a command of the form:
DROP TABLE IF EXISTS table_a, table_b, ... , table_n CASCADE;
Using generate_series() to generate requested table names. More here:
answered Nov 29, 2018 at 3:54
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
Comments
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT oid::REGCLASS table_name
FROM pg_class
WHERE relname <= 'table_x'
AND relkind = 'r'
LOOP
EXECUTE 'DROP TABLE' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
