Re: How to do a "CREATE DATABASE" and then connect to it?

Terry Lee Tucker <terry(at)esc1(dot)com> wrote:

> Joseph Brenner <doom(at)kzsu(dot)stanford(dot)edu> wrote:

> > > Joseph Brenner wrote:
> >
> > > > After you do a "CREATE DATABASE", how do you programatically
> > > > connect to what you just created?

> > It's not a terribly major point, I'm just wondering if it's true that
> > there's no postgres SQL analog of the psql "\c" command.
> >
> > For example, this certainly works in perl:
> >
> > use DBI;
> >
> > my $dbh_1 = DBI->connect("dbi:Pg:dbname=template1", $owner, $db_password,
> > { RaiseError => 1, AutoCommit => 1 });
> > $dbh_1->do("CREATE DATABASE new_test_db");
> >
> > $dbh_1->disconnect();
> >
> > my $dbh_2 = DBI->connect("dbi:Pg:dbname=new_test_db", $owner, $db_password,
> > { RaiseError => 1, AutoCommit => 1 });
> >
> > $dbh_2->do("CREATE TABLE whocares (meaningless INTEGER, blather TEXT)"); >
> >
> > But the need for those two "DBI->connect"s seems inelegant to me.

> I don't see the point. There are two connects either way.
> psql:
> Connection 1: psql template1
> Connection 2: \c new_test_db
>
> Perl:
> Connection 1: my $dbh_1 = DBI->connect("dbi:Pg:dbname=template1", blah, blah
> Connection 2: DBI->connect("dbi:Pg:dbname=new_test_db", $owner, blah, blah
>
> The only difference, at least from my point of view, is the method used and
> the extra disconnect(). But even so, the extra disconnect() is not really
> necessary.

I think there are two different "connects" we're talking about here,
one is the connection to the postgresql, the other is the "connection"
to the "database" (i.e. the "dbname", which probably should've been
called the "catalog").

My guess (and it's only a guess) is that connecting to the postmaster
is relatively expensive, and that a (hypothetical) "CONNECT <dbname>"
command would be much faster.

This isn't a big point with me (at least at the moment) because while I
may be pragmatically creating/using/dropping databases soon, I'm not
likely to be doing this in production code.

I was just coming back to postgres after doing other things for awhile,
and the absence of something like a "CONNECT <dbname>" in it's SQL
struck me as a little odd.