Mark Forums Read

drop table 'foo%' ...

Go Back

comp.databases.postgresql comp.databases.postgresql

Reply

#1

Old

Thomas Guettler

Posts: n/a

drop table 'foo%' ... - 03-04-2008 , 04:01 AM

I want to drop all tables which start with a common prefix. I found this solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;' FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Thomas

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de

Reply With Quote

#2

Old

Laurenz Albe

Posts: n/a

Re: drop table 'foo%' ... - 03-04-2008 , 04:44 AM

Thomas Guettler <hv (AT) tbz-pariv (DOT) de> wrote:

Quote:

I want to drop all tables which start with a common prefix. I found this
solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;'
FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Basically I think that this solution is good, but I'd like to remark that
you do not restrict the search to a specific schema, so you might end up
deleting (or trying to delete) system tables that happen to have the same
prefix (e.g., your prefix shouldn't be 'pg_%').

I guess you are aware of this; it might be a good idea to examine
"droptables" before running it.

Yours,
Laurenz Albe

Reply With Quote

#3

Old

Laurenz Albe

Posts: n/a

Re: drop table 'foo%' ... - 03-04-2008 , 04:44 AM

Thomas Guettler <hv (AT) tbz-pariv (DOT) de> wrote:

Quote:

I want to drop all tables which start with a common prefix. I found this
solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;'
FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Basically I think that this solution is good, but I'd like to remark that
you do not restrict the search to a specific schema, so you might end up
deleting (or trying to delete) system tables that happen to have the same
prefix (e.g., your prefix shouldn't be 'pg_%').

I guess you are aware of this; it might be a good idea to examine
"droptables" before running it.

Yours,
Laurenz Albe

Reply With Quote

#4

Old

Laurenz Albe

Posts: n/a

Re: drop table 'foo%' ... - 03-04-2008 , 04:44 AM

Thomas Guettler <hv (AT) tbz-pariv (DOT) de> wrote:

Quote:

I want to drop all tables which start with a common prefix. I found this
solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;'
FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Basically I think that this solution is good, but I'd like to remark that
you do not restrict the search to a specific schema, so you might end up
deleting (or trying to delete) system tables that happen to have the same
prefix (e.g., your prefix shouldn't be 'pg_%').

I guess you are aware of this; it might be a good idea to examine
"droptables" before running it.

Yours,
Laurenz Albe

Reply With Quote

#5

Old

Laurenz Albe

Posts: n/a

Re: drop table 'foo%' ... - 03-04-2008 , 04:44 AM

Thomas Guettler <hv (AT) tbz-pariv (DOT) de> wrote:

Quote:

I want to drop all tables which start with a common prefix. I found this
solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;'
FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Basically I think that this solution is good, but I'd like to remark that
you do not restrict the search to a specific schema, so you might end up
deleting (or trying to delete) system tables that happen to have the same
prefix (e.g., your prefix shouldn't be 'pg_%').

I guess you are aware of this; it might be a good idea to examine
"droptables" before running it.

Yours,
Laurenz Albe

Reply With Quote

#6

Old

Laurenz Albe

Posts: n/a

Re: drop table 'foo%' ... - 03-04-2008 , 04:44 AM

Thomas Guettler <hv (AT) tbz-pariv (DOT) de> wrote:

Quote:

I want to drop all tables which start with a common prefix. I found this
solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;'
FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Basically I think that this solution is good, but I'd like to remark that
you do not restrict the search to a specific schema, so you might end up
deleting (or trying to delete) system tables that happen to have the same
prefix (e.g., your prefix shouldn't be 'pg_%').

I guess you are aware of this; it might be a good idea to examine
"droptables" before running it.

Yours,
Laurenz Albe

Reply With Quote

#7

Old

Laurenz Albe

Posts: n/a

Re: drop table 'foo%' ... - 03-04-2008 , 04:44 AM

Thomas Guettler <hv (AT) tbz-pariv (DOT) de> wrote:

Quote:

I want to drop all tables which start with a common prefix. I found this
solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;'
FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Basically I think that this solution is good, but I'd like to remark that
you do not restrict the search to a specific schema, so you might end up
deleting (or trying to delete) system tables that happen to have the same
prefix (e.g., your prefix shouldn't be 'pg_%').

I guess you are aware of this; it might be a good idea to examine
"droptables" before running it.

Yours,
Laurenz Albe

Reply With Quote

#8

Old

Laurenz Albe

Posts: n/a

Re: drop table 'foo%' ... - 03-04-2008 , 04:44 AM

Thomas Guettler <hv (AT) tbz-pariv (DOT) de> wrote:

Quote:

I want to drop all tables which start with a common prefix. I found this
solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;'
FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Basically I think that this solution is good, but I'd like to remark that
you do not restrict the search to a specific schema, so you might end up
deleting (or trying to delete) system tables that happen to have the same
prefix (e.g., your prefix shouldn't be 'pg_%').

I guess you are aware of this; it might be a good idea to examine
"droptables" before running it.

Yours,
Laurenz Albe

Reply With Quote

#9

Old

Laurenz Albe

Posts: n/a

Re: drop table 'foo%' ... - 03-04-2008 , 04:44 AM

Thomas Guettler <hv (AT) tbz-pariv (DOT) de> wrote:

Quote:

I want to drop all tables which start with a common prefix. I found this
solution.
Are there better/different ones?

psql -t -d mydb -c "SELECT 'DROP TABLE ' || tablename || ' CASCADE ;'
FROM pg_catalog.pg_tables where tablename like
'myprefix_%'" > droptables

psql -d mydb -f droptables

Basically I think that this solution is good, but I'd like to remark that
you do not restrict the search to a specific schema, so you might end up
deleting (or trying to delete) system tables that happen to have the same
prefix (e.g., your prefix shouldn't be 'pg_%').

I guess you are aware of this; it might be a good idea to examine
"droptables" before running it.

Yours,
Laurenz Albe

Reply With Quote

Reply

« Previous Thread | Next Thread »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.