pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to drop automatically some databases?
2+ messages / 2 participants
[nested] [flat]
* How to drop automatically some databases?
@ 2015-04-25 12:07 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas @ 2015-04-25 12:07 UTC (permalink / raw)
To: pgsql-admin
I've got a developer PC (Windows) that hosts copies of a production
database so I can tinker with it without causing damage to the
production database.
Every night a script fetches a dump of the production database.
Then it renames the test-db on my PC, creates a new test-db and imports
the dump.
The current version is named "db_test".
The older copies are named "db_test_yymmdd_hhmm". The name is extended
with the timestamp of the import.
Now I'd like to keep just 3 older copies in case I want to look up
structural moifications I've made the day before.
Older copies should get dropped.
This would show the names to drop.
select datname
from (
select datname
from pg_database
where datname ilike 'db_test_%'
order by datname
) as x
EXCEPT
select datname
from (
select datname
from pg_database
where datname ilike 'db_test_%'
order by datname desc
limit 3
) as y;
I tried to run this
drop database ( ... the select above ... )
in the DB "postgres" but it didn't work.
After a couple of seconds I get a syntax error.
What now? :}
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to drop automatically some databases?
@ 2015-04-25 16:25 David G. Johnston <david.g.johnston@gmail.com>
parent: Andreas <maps.on@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2015-04-25 16:25 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-admin
On Saturday, April 25, 2015, Andreas <maps.on@gmx.net> wrote:
> drop database ( ... the select above ... )
>
> in the DB "postgres" but it didn't work.
> After a couple of seconds I get a syntax error.
>
> What now? :}
>
>
You have to use dynamic sql - either using plpgsql so something in your
client of choice (e.g. psql). Drop database is DSL and does not accept
database names as parameters or via column references.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-04-25 16:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-04-25 12:07 How to drop automatically some databases? Andreas <maps.on@gmx.net>
2015-04-25 16:25 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox