Hoe kan ik alle tabellen in PostgreSQL laten vallen, werkend vanaf de opdrachtregel?
Ik wil nietde database zelf verwijderen, alleen alle tabellen en alle gegevens erin.
Antwoord 1, autoriteit 100%
Als al uw tabellen in één schema staan, zou deze aanpak kunnen werken (onder de code wordt aangenomen dat de naam van uw schema public
is)
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
Als u PostgreSQL 9.3 of hoger gebruikt, moet u mogelijk ook de standaardtoekenningen herstellen.
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
Antwoord 2, autoriteit 30%
U kunt een query schrijven om een SQL-script als volgt te genereren:
select 'drop table "' || tablename || '" cascade;' from pg_tables;
Of:
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;
In het geval dat sommige tabellen automatisch worden verwijderd vanwege de trapsgewijze optie in een vorige zin.
Bovendien wilt u, zoals vermeld in de opmerkingen, de tabellen die u wilt neerzetten filteren op schemanaam:
select 'drop table if exists "' || tablename || '" cascade;'
from pg_tables
where schemaname = 'public'; -- or any other schema
En voer het dan uit.
Glorious COPY+PASTE werkt ook.
Antwoord 3, autoriteit 20%
Het meest geaccepteerde antwoord op dit moment (januari 2014) is:
drop schema public cascade;
create schema public;
Dit werkt wel, maar als het je bedoeling is om het openbare schema in zijn maagdelijke staat te herstellen, is de taak niet volledig volbracht. Als u onder pgAdmin III voor PostgreSQL 9.3.1 op het op deze manier gemaakte “openbare” schema klikt en in het “SQL-venster” kijkt, ziet u het volgende:
-- Schema: public
-- DROP SCHEMA public;
CREATE SCHEMA public
AUTHORIZATION postgres;
Echter, een gloednieuwe database heeft het volgende:
-- Schema: public
-- DROP SCHEMA public;
CREATE SCHEMA public
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public
IS 'standard public schema';
Voor mij veroorzaakte het gebruik van een python-webframework dat databasetabellen (web2py) maakt, problemen:
<class 'psycopg2.ProgrammingError'> no schema has been selected to create in
Dus naar mijn mening is het volledig juiste antwoord:
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';
Ook voor het geven van deze commando’s in pgAdmin III heb ik de Query-tool gebruikt ( vergrootglaspictogram “Execute abritrary SQL queries”) of u kunt Plugins-> PSQL-console
Opmerking
Als je extensies hebt geïnstalleerd, worden deze verwijderd wanneer je het schema verwijdert, dus je moet noteren wat je moet installeren en vervolgens instructies uitvoeren als dat nodig is. Bijv.
CREATE EXTENSION postgis;
Antwoord 4, autoriteit 14%
Je kunt alle tafels laten vallen met
DO $$ DECLARE
r RECORD;
BEGIN
-- if the schema you operate on is not "current", you will want to
-- replace current_schema() in query with 'schematodeletetablesfrom'
-- *and* update the generate 'DROP...' accordingly.
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END $$;
IMO dit is beter dan drop schema public
, omdat je het schema
niet opnieuw hoeft te maken en niet alle toewijzingen hoeft te herstellen.
Extra bonus dat dit geen externe scripttaal vereist, noch het kopiëren en plakken van gegenereerde SQL naar de interpreter.
Antwoord 5, autoriteit 9%
Als alles wat u wilt verwijderen eigendomis van dezelfde gebruiker, kunt u het volgende gebruiken:
drop owned by the_user;
Hiermee wordt allesverwijderd dat de gebruiker bezit.
Dat omvat gematerialiseerde weergaven, weergaven, reeksen, triggers, schema’s, functies, typen, aggregaten, operators, domeinen enzovoort (dus eigenlijk: alles) dat the_user
bezit (= gemaakt).
Je moet the_user
vervangen door de daadwerkelijke gebruikersnaam, momenteel is er geen optie om alles voor “de huidige gebruiker” te laten vallen. De aankomende 9.5-versie heeft de optie drop owned by current_user
.
Meer details in de handleiding: http://www. postgresql.org/docs/current/static/sql-drop-owned.html
Antwoord 6, autoriteit 5%
Zoals Pablo hierboven, om gewoon uit een specifiek schema te stappen, met betrekking tot het geval:
select 'drop table "' || tablename || '" cascade;'
from pg_tables where schemaname = 'public';
Antwoord 7, autoriteit 4%
Dit is een heel interessante vraag, en je krijgt hem op meerdere manieren voor elkaar:
1. Door het huidige schema te laten vallen en opnieuw te maken
Hier, Over het algemeen hebben we standaard een public
schema. Dus ik gebruik het als een instantie.
DROP SCHEMA `public` CASCADE;
CREATE SCHEMA `public`;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
Als u PostgreSQL 9.3 of hoger gebruikt, moet u mogelijk ook de standaardtoekenningen herstellen.
Voors:
Hiermee wordt een heel schema opgeschoond en opnieuw gemaakt als een nieuw schema.
Nadelen:
Je verliest ook andere entiteiten zoals Functions
, Views
, Materialized views
, enz.
2. Door gebruik te maken van het ophalen van alle tabelnamen uit de pg_tables
tabel.
PostgreSQL slaat alle tabellen op in de recordtabel met de naam pg_table
.
SELECT
'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;'
from
pg_tables WHERE schemaname = 'public';
Zoals u kunt zien, kunnen we door het gebruik van subquery’s de volledige tabellen uit het schema verwijderen.
Voors:
Als de andere gegevensentiteiten belangrijk zijn en u alleen tabellen uit het schema wilt verwijderen, is deze aanpak erg nuttig voor u.
3. Terminal
- Log in met postgres-gebruiker op uw shell
$ sudo -u postgres psql
- Verbind uw database
$ \c mydatabase
Plak deze commando’s:
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
Opmerking: deze reeks opdrachten is vergelijkbaar met het eerste punt, dus de voor- en nadelen blijven hetzelfde.
Antwoord 8, autoriteit 3%
drop schema public cascade;
zou moeten lukken.
Antwoord 9, autoriteit 2%
In navolging van Pablo en LenW is hier een one-liner die alles zowel voorbereiden als uitvoeren doet:
psql -U $PGUSER $PGDB -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname = 'public'" | psql -U $PGUSER $PGDB
NB: stel of vervang $PGUSER
en $PGDB
door de gewenste waarden
Antwoord 10, autoriteit 2%
De volgende stappen kunnen nuttig zijn (voor linux-gebruikers):
-
Voer eerst de opdrachtprompt
postgres
in door het volgende commando te volgen:sudo -u postgres psql
-
Voer de database in met dit commando (mijn databasenaam is:
maoss
):\c maoss
-
Voer nu het commando in om alle tabellen te verwijderen:
DROP SCHEMA public CASCADE; CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public;
Antwoord 11, autoriteit 2%
Als je de PL/PGSQL-proceduretaal geïnstalleerdhebt je kunt het volgende gebruiken om alles te verwijderen zonder een shell/Perl extern script.
DROP FUNCTION IF EXISTS remove_all();
CREATE FUNCTION remove_all() RETURNS void AS $$
DECLARE
rec RECORD;
cmd text;
BEGIN
cmd := '';
FOR rec IN SELECT
'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) || ' CASCADE;' AS name
FROM
pg_catalog.pg_class AS c
LEFT JOIN
pg_catalog.pg_namespace AS n
ON
n.oid = c.relnamespace
WHERE
relkind = 'S' AND
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)
LOOP
cmd := cmd || rec.name;
END LOOP;
FOR rec IN SELECT
'DROP TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) || ' CASCADE;' AS name
FROM
pg_catalog.pg_class AS c
LEFT JOIN
pg_catalog.pg_namespace AS n
ON
n.oid = c.relnamespace WHERE relkind = 'r' AND
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)
LOOP
cmd := cmd || rec.name;
END LOOP;
FOR rec IN SELECT
'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
|| quote_ident(proname) || '(' || oidvectortypes(proargtypes)
|| ');' AS name
FROM
pg_proc
INNER JOIN
pg_namespace ns
ON
(pg_proc.pronamespace = ns.oid)
WHERE
ns.nspname =
'public'
ORDER BY
proname
LOOP
cmd := cmd || rec.name;
END LOOP;
EXECUTE cmd;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT remove_all();
In plaats van dit in te typen bij de “psql”-prompt, raad ik u aan het naar een bestand te kopiëren en het bestand vervolgens als invoer door te geven aan psql met behulp van de “–file” of “-f” opties:
psql -f clean_all_pg.sql
Tegoed waar de eer toekomt: ik heb de functie geschreven, maar denk dat de vragen (of de eerste tenminste) jaren geleden afkomstig waren van iemand op een van de pgsql-mailinglijsten. Weet niet meer precies wanneer of welke.
Antwoord 12
Als je toch alle tabellen wilt vernietigen, kun je afzien van aardigheden zoals CASCADE door alle tabellen in één statement te plaatsen. Dit maakt de uitvoering ook sneller.
SELECT 'TRUNCATE TABLE ' || string_agg('"' || tablename || '"', ', ') || ';'
FROM pg_tables WHERE schemaname = 'public';
Direct uitvoeren:
DO $$
DECLARE tablenames text;
BEGIN
tablenames := string_agg('"' || tablename || '"', ', ')
FROM pg_tables WHERE schemaname = 'public';
EXECUTE 'TRUNCATE TABLE ' || tablenames;
END; $$
Vervang TRUNCATE
door DROP
zoals van toepassing.
Antwoord 13
Ik heb Pablo’s antwoord iets aangepast om de gegenereerde SQL-commando’s als één enkele string terug te laten komen:
select string_agg('drop table "' || tablename || '" cascade', '; ')
from pg_tables where schemaname = 'public'
Antwoord 14
Gebruik dit script in pgAdmin:
DO $$
DECLARE
brow record;
BEGIN
FOR brow IN (select 'drop table "' || tablename || '" cascade;' as table_name from pg_tables where schemaname = 'public') LOOP
EXECUTE brow.table_name;
END LOOP;
END; $$
Antwoord 15
Voor het geval… Eenvoudig Python-script dat de Postgresql-database opschoont
import psycopg2
import sys
# Drop all tables from a given database
try:
conn = psycopg2.connect("dbname='akcja_miasto' user='postgres' password='postgres'")
conn.set_isolation_level(0)
except:
print "Unable to connect to the database."
cur = conn.cursor()
try:
cur.execute("SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name")
rows = cur.fetchall()
for row in rows:
print "dropping table: ", row[1]
cur.execute("drop table " + row[1] + " cascade")
cur.close()
conn.close()
except:
print "Error: ", sys.exc_info()[1]
Zorg ervoor dat na het kopiëren de inspringing juist is, aangezien Python erop vertrouwt.
Antwoord 16
Je kunt de string_agg-functie gebruiken om een door komma’s gescheiden lijst te maken, perfect voor DROP TABLE.
Van een bash-script:
#!/bin/bash
TABLES=`psql $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public'"`
echo Dropping tables:${TABLES}
psql $PGDB --command "DROP TABLE IF EXISTS ${TABLES} CASCADE"
17
Als u gegevens wilt verwijderen (geen tabel verwijderen):
-- Truncate tables and restart sequnces
SELECT 'TRUNCATE TABLE "' || table_schema || '"."' || table_name || '" RESTART IDENTITY CASCADE;'
FROM information_schema.tables
WHERE table_catalog = '<database>' AND table_schema = '<schema>';
of als u een drop-tabel wilt, kunt u deze SQL gebruiken:
-- For tables
SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;'
FROM information_schema.tables
WHERE table_catalog = '<database>' AND table_schema = '<schema>';
-- For sequences
SELECT 'DROP SEQUENCE d_a_seq "' || sequence_schema || '"."' || sequence_name || '";'
FROM information_schema.sequences
WHERE sequence_catalog = '<database>' AND sequence_schema = '<schema>';
18
U moet tabellen en sequenties laten vallen, hier is wat voor mij gewerkt
psql -qAtX -c "select 'DROP TABLE IF EXISTS ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' FROM information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX
psql -qAtX -c "select 'DROP SEQUENCE IF EXISTS ' || quote_ident(relname) || ' CASCADE;' from pg_statio_user_sequences;" | psql -qAtX
voordat u de opdracht uitvoert, moet u mogelijk sudo/su naar de postgres
-gebruiker of (exporteer verbindingsdetails PGHOST
, PGPORT
, PGUSER
en PGPASSWORD
) en vervolgens export PGDATABASE=yourdatabase
Antwoord 19
Rake-taak voor Rails om alle tabellen in de huidige database te vernietigen
namespace :db do
# rake db:drop_all_tables
task drop_all_tables: :environment do
query = <<-QUERY
SELECT
table_name
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND
table_schema NOT IN ('pg_catalog', 'information_schema');
QUERY
connection = ActiveRecord::Base.connection
results = connection.execute query
tables = results.map do |line|
table_name = line['table_name']
end.join ", "
connection.execute "DROP TABLE IF EXISTS #{ tables } CASCADE;"
end
end
Antwoord 20
Ik heb de bash-methode van jamie verbeterd door voor views te zorgen, omdat hij alleen het tabeltype “base table” respecteert, wat de standaard is.
door de bash-code te volgen, worden eerst de weergaven verwijderd en daarna de rest
#!/usr/bin/env bash
PGDB="yourDB"
# By exporting user & pass your dont need to interactively type them on execution
export PGUSER="PGusername"
export PGPASSWORD="PGpassword"
VIEWS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW'"`
BASETBLS=`psql -d $PGDB -t --command "SELECT string_agg(table_name, ',') FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"`
echo Dropping views:${VIEWS}
psql $PGDB --command "DROP VIEW IF EXISTS ${VIEWS} CASCADE"
echo Dropping tables:${BASETBLS}
psql $PGDB --command "DROP TABLE IF EXISTS ${BASETBLS} CASCADE"
21
In een Windows-batchbestand:
@echo off
FOR /f "tokens=2 delims=|" %%G IN ('psql --host localhost --username postgres --command="\dt" YOUR_TABLE_NAME') DO (
psql --host localhost --username postgres --command="DROP table if exists %%G cascade" sfkb
echo table %%G dropped
)
22
Nou, omdat ik het leuk vind om vanuit de opdrachtregel te werken …
psql -U <user> -d <mydb> -c '\dt' | cut -d ' ' -f 4 | sed -e "s/^/drop table if exists /" | sed -e "s/$/;/"
-c '\dt'
roept de opdracht van de lijsttabellen aan.
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+----------
public | _d_psidxddlparm | table | djuser
public | _d_psindexdefn | table | djuser
cut -d ' ' -f 4
NU PUBLIPEN DE OUTUITWERKING OM HET 4DE GEBIED TE GRAKEN (bij gebruik van ruimte als separator), wat de tabel is.
sed
wordt vervolgens gebruikt om een drop table
en suffix de ;
opdrachtscheider te geven.
| egrep '_d_'
– pijp het in grep
wat meer en je kunt selectiever zijn over welke tabellen die je daalt.
drop table if exists _d_psidxddlparm;
drop table if exists _d_psindexdefn;
Opmerking: zoals geschreven, genereert dit valse rijen voor de uitvoer van de \dt
-opdrachten van kolomkoppen en totale rijen aan het einde. Ik vermijd dat door te greppen, maar je zou head
en tail
kunnen gebruiken.
Antwoord 23
De gemakkelijkste manier is om het openbare schema te verwijderen, zoals anderen in eerdere antwoorden hebben gesuggereerd. Dit is echter GEEN goede manier. Je weet nooit wat er is gedaan met het openbare schema dat sindsdien is vergeten en niet is gedocumenteerd. Je weet ook niet of dit in de toekomst hetzelfde zal werken. In V9 zou het prima zijn geweest, maar in V10 zouden al uw gebruikers de toegang tot het schema verliezen en moeten ze opnieuw toegang krijgen, anders zal uw toepassing kapot gaan. Ik heb V11 niet gecontroleerd, maar het punt is dat je nooit weet wat er kapot gaat als je van machine naar machine, van site naar site of van versie naar versie gaat. Het kan ook niet worden gedaan als u een gebruiker bent die toegang heeft tot de database, maar niet tot het schema.
Als u dit programmatisch moet doen, dekken andere bovenstaande antwoorden dit, maar een ding dat in de bovenstaande antwoorden niet wordt overwogen, is om Postgres het werk voor u te laten doen. Als je pg_dump gebruikt met de -c optie zoals hieronder:
sudo su postgres -c "pg_dump -U postgres WhateverDB -c -f "/home/Anyone/DBBackupWhateverDB-ServerUnscheduled.sql""
Dat zal een DB-herstelscript maken met sql-instructies die alle tabellen zullen verwijderen.
Als het enige doel van het stellen van de vraag was om de tabellen te verwijderen voorafgaand aan het herstel, dan zal uw herstel het werk voor u doen.
Als je het echter voor iets anders nodig hebt, kun je gewoon de drop-statements uit het sql-script kopiëren.
Antwoord 24
Een op een terminal gebaseerde aanpak met behulp van psql
werkte het beste voor mij. Ik heb zelfs een bash-functie gemaakt omdat het handig is voor ontwikkeling:
psqlDropTables() {
PGPASSWORD=<your password>
PGTABLE=<your table name>
PGUSER=<your pg user name>
PGPASSWORD=$PGPASSWORD psql -ah 127.0.0.1 $PGTABLE $PGUSER -c "
SELECT
'DROP TABLE IF EXISTS \"' || tablename || '\" CASCADE;' from
pg_tables WHERE schemaname = 'public';" | grep DROP | awk 'NR>1{print $0}' | sed "s/\"/'/g" | PGPASSWORD=$PGPASSWORD xargs -i psql -ah 127.0.0.1 $PGTABLE $PGUSER -c {}
}
Het creëert alle vereiste drop table statements zoals vermeld in deze reactie, vervangt de ” door ‘ en voert ze uit op de database.
Antwoord 25
Misschien is de eenvoudigste manier:
-
Drop-database bevat tabellen met:
drop database DATABASE_NAME;
-
Maak die database opnieuw:
create database DATABASE_NAME;
Antwoord 26
Hier is de kant-en-klare vraag voor u:
SELECTEER
'drop table if exists "' || tablename || '" cascade;' as pg_drop
VAN
pg_tables
WAAR
schemaname='your schema';
Antwoord 27
voor macOS. Als je een terminal hebt via de PostgreSQL-toepassing, kan een eenvoudige opdracht je helpen:
drop table "organisations" cascade;