I've read the documentation on this here so I'm 90% sure, but the docs don't explicitly say if there's a limit to the list size, or if lists can also have schema names in the name formatting. I just want to be positive before I accidentally mess up my database or delete everything in a way I can't recover.
To drop a large list of tables (nearly 200) in postgreSQL, is the SQL command:
DROP TABLE schemaA.table_name, schemaB.tableA_name, schemaC.table_name, schemaC.tableB_name;
Would appreciate if someone could confirm. Thanks!
You should be ok with 200 table list with schemas. Also wrap up drop to transaction if you have doubts. Postgres supports DDL in transactions. Below is my successful run...
prepare:
t=# create table so(i int);
CREATE TABLE
t=# do $$ begin for i in 1..200 loop execute 'create table sol'||i||' as select 8 from so where false'; end loop; end; $$;
DO
select string_agg('public.'||tablename, ', ') from pg_tables where tablename like 'sol%';
run:
t=# drop table public.sol3, public.sol4, public.sol5, public.sol6, public.sol7, public.sol8, public.sol9, public.sol10, public.sol1, public.sol2, public.sol11, public.sol12, public.sol13, public.sol14, public.sol15, public.sol16, public.sol17, public.sol18, public.sol19, public.sol20, public.sol21, public.sol22, public.sol23, public.sol24, public.sol25, public.sol26, public.sol27, public.sol28, public.sol29, public.sol46, public.sol47, public.sol30, public.sol31, public.sol32, public.sol33, public.sol34, public.sol35, public.sol36, public.sol37, public.sol38, public.sol39, public.sol40, public.sol41, public.sol42, public.sol43, public.sol44, public.sol45, public.sol48, public.sol49, public.sol50, public.sol51, public.sol52, public.sol53, public.sol54, public.sol55, public.sol56, public.sol57, public.sol58, public.sol59, public.sol60, public.sol61, public.sol62, public.sol63, public.sol64, public.sol65, public.sol66, public.sol67, public.sol68, public.sol69, public.sol70, public.sol71, public.sol72, public.sol73, public.sol74, public.sol75, public.sol76, public.sol77, public.sol78, public.sol79, public.sol80, public.sol81, public.sol82, public.sol83, public.sol84, public.sol85, public.sol86, public.sol87, public.sol88, public.sol89, public.sol90, public.sol91, public.sol92, public.sol93, public.sol94, public.sol95, public.sol96, public.sol97, public.sol98, public.sol99, public.sol100, public.sol101, public.sol102, public.sol103, public.sol104, public.sol105, public.sol106, public.sol107, public.sol108, public.sol109, public.sol110, public.sol111, public.sol112, public.sol113, public.sol114, public.sol115, public.sol116, public.sol117, public.sol118, public.sol119, public.sol120, public.sol121, public.sol122, public.sol123, public.sol124, public.sol125, public.sol126, public.sol127, public.sol128, public.sol129, public.sol130, public.sol131, public.sol132, public.sol133, public.sol134, public.sol135, public.sol136, public.sol137, public.sol138, public.sol139, public.sol140, public.sol141, public.sol142, public.sol143, public.sol144, public.sol145, public.sol146, public.sol147, public.sol148, public.sol149, public.sol150, public.sol151, public.sol152, public.sol153, public.sol154, public.sol155, public.sol156, public.sol157, public.sol158, public.sol159, public.sol160, public.sol161, public.sol162, public.sol163, public.sol164, public.sol165, public.sol166, public.sol167, public.sol168, public.sol169, public.sol170, public.sol171, public.sol172, public.sol173, public.sol174, public.sol175, public.sol176, public.sol177, public.sol178, public.sol179, public.sol180, public.sol181, public.sol182, public.sol183, public.sol184, public.sol185, public.sol186, public.sol187, public.sol188, public.sol189, public.sol190, public.sol191, public.sol192, public.sol193, public.sol194, public.sol195, public.sol196, public.sol197, public.sol198, public.sol199, public.sol200;
DROP TABLE
check:
t=# select string_agg('public.'||tablename, ', ') from pg_tables where tablename like 'sol%'; string_agg
------------
(1 row)