1

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!

singmotor
  • 3,930
  • 12
  • 45
  • 79
  • 1
    Table name is *The name (optionally schema-qualified) of the table to drop*, so you can use schema names. I've never dropped 200 tables at once, but if there is a limit for a list, the command simply will not be executed, no risk. – klin May 02 '17 at 20:58
  • @klin and my format of schemaName.tableName is correct? Thank you for the patience, just don't want to be the guy that destroys the DB – singmotor May 02 '17 at 21:00
  • 1
    Yes, *schema_name.table_name*. – klin May 02 '17 at 21:01

1 Answers1

0

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)
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • thanks for the response, could you explain what it means to 'wrap up to transaction'? – singmotor May 03 '17 at 13:52
  • 1
    I mean you can run `BEGIN; drop t1,t2,t3...t200; SOME_CUSTOM_QUERIES_TO_CKECK; COMMIT;` or `ROLLBACK` if checks failed – Vao Tsun May 03 '17 at 13:54