Thread: Empty database creation???

Hello,

I'm having what I consider a strange problem, although it may be
caused by my being such a newby to Postgres.  I have been asked to
send a empty database to a colleague by a researcher.  The first time
I created it, I didn't realize they want it empty.  I created the
database and then loaded a file I created with pg_dump.  Then I found
out that the researcher want to send an empty database.  I dropped
the database and ran the pg_dump with the flag to only give me the
schema.  Then I re-created the database and loaded the schema only
file.  Then I did a select count to make sure the tables were empty,
but they had their data.  I figured I had made a mistake so went
through the entire process again and got exactly the same result.
Then I wondered where the glitch was so I dropped the database then
recreated it, without restoring the database.  When I looked at the
database all the tables were there and populated.  Next I dropped the
database, bailed out of postgres, started psql again recreated the
database.  The tables were there and populated.  I've even made sure
the database was dropped by listing the databases.  Still, when I
recreate it, it comes with all the tables there and populated.  I've
worked with a lot of databases and everything in my experience says
that when a database is dropped it's gone.  Why does this come back
with all the tables intact when I drop it and recreate a database
with the same name?  Is there a way I can this to work?  I have not
tried creating the empty database under a name I haven't used, which
I can do.  It's just that this seems really weird to me.

Carol Walter

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 19 Mar 2008 17:21:39 -0400
Carol Walter <walterc@indiana.edu> wrote:

 The tables were there and populated.  I've
> even made sure the database was dropped by listing the databases.
> Still, when I recreate it, it comes with all the tables there and
> populated.  I've worked with a lot of databases and everything in my
> experience says that when a database is dropped it's gone.  Why does
> this come back with all the tables intact when I drop it and recreate
> a database with the same name?  Is there a way I can this to work?  I
> have not tried creating the empty database under a name I haven't
> used, which I can do.  It's just that this seems really weird to me.

You probably have template1 with data in it.

Joshua D. Drake


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH4YzLATb/zqfZUUQRAvpjAKCSLje0tGAphePMMvr2sH8aaZyTGACfYHga
zcPXk8u01igHmUP2tZRYNIo=
=WZMs
-----END PGP SIGNATURE-----

Carol Walter <walterc@indiana.edu> writes:
> ... Why does this come back
> with all the tables intact when I drop it and recreate a database
> with the same name?

Most likely, you accidentally created all that stuff in template1,
and CREATE DATABASE is just copying it from there.  Clean out template1.

            regards, tom lane