Thread: How to do a "CREATE DATABASE" and then connect to it?
After you do a "CREATE DATABASE", how do you programatically connect to what you just created? In the psql monitor, you'd use the "\c" command. If the DATABASE already exists when you connect to postgresql, you use the name when you connect (e.g. "dbname=..."). I'm getting the impression I need to do this in multiple steps, which is workable, but seems a little silly.
Joseph Brenner wrote:
> After you do a "CREATE DATABASE", how do you programatically
> connect to what you just created?
>
> In the psql monitor, you'd use the "\c" command.
>
> If the DATABASE already exists when you connect to postgresql,
> you use the name when you connect (e.g. "dbname=...").
>
> I'm getting the impression I need to do this in multiple steps,
> which is workable, but seems a little silly.
>
Have you tried reading the documentation?
Sincerely,
Joshua D. Drake
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Joshua D. Drake <jd@commandprompt.com> wrote:
> Joseph Brenner wrote:
> > After you do a "CREATE DATABASE", how do you programatically
> > connect to what you just created?
> >
> > In the psql monitor, you'd use the "\c" command.
> >
> > If the DATABASE already exists when you connect to postgresql,
> > you use the name when you connect (e.g. "dbname=...").
> >
> > I'm getting the impression I need to do this in multiple steps,
> > which is workable, but seems a little silly.
> Have you tried reading the documentation?
Yup. Are you sure you've understood my question?
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.
Do you have any particular portion of the documentation in mind?
On Saturday 03 June 2006 04:07 am, Joseph Brenner <doom@kzsu.stanford.edu>
thus communicated:
-->
--> Joshua D. Drake <jd@commandprompt.com> wrote:
-->
--> > Joseph Brenner wrote:
-->
--> > > After you do a "CREATE DATABASE", how do you programatically
--> > > connect to what you just created?
--> > >
--> > > In the psql monitor, you'd use the "\c" command.
--> > >
--> > > If the DATABASE already exists when you connect to postgresql,
--> > > you use the name when you connect (e.g. "dbname=...").
--> > >
--> > > I'm getting the impression I need to do this in multiple steps,
--> > > which is workable, but seems a little silly.
-->
--> > Have you tried reading the documentation?
-->
--> Yup. Are you sure you've understood my question?
-->
--> 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.
-->
--> Do you have any particular portion of the documentation in mind?
-->
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.
On Sat, 3 Jun 2006 04:36:59 -0400 Terry Lee Tucker <terry@esc1.com> thought long, then sat down and wrote:
> On Saturday 03 June 2006 04:07 am, Joseph Brenner <doom@kzsu.stanford.edu>
> thus communicated:
> -->
> --> Joshua D. Drake <jd@commandprompt.com> wrote:
> -->
> --> > Joseph Brenner wrote:
> -->
> --> > > After you do a "CREATE DATABASE", how do you programatically
> --> > > connect to what you just created?
> --> > >
> --> > > In the psql monitor, you'd use the "\c" command.
> --> > >
> --> > > If the DATABASE already exists when you connect to postgresql,
> --> > > you use the name when you connect (e.g. "dbname=...").
> --> > >
> --> > > I'm getting the impression I need to do this in multiple steps,
> --> > > which is workable, but seems a little silly.
> -->
> --> > Have you tried reading the documentation?
> -->
> --> Yup. Are you sure you've understood my question?
> -->
> --> 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.
> -->
> --> Do you have any particular portion of the documentation in mind?
> -->
>
> 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 the point is, that one has not to leave psql to get the new connection, but simply use \c, while with perl /
anyprogramming language you have to open a new coennection with a long and complicated command.
But "\c" is _not_ an SQL-command, its simply a shortcut for closing an old connection and creating a new one. You could
dosomething similar in your program creating a function as shortcut to avoid lots of typing everytime you change
connection.This function would need $dbh and the new database as parameter, close the old connection and return the new
one.That__s equivalent to what "\c" does.
It would sometimes really be nice, btw, if inter-database queries were possible.
--
Frank Finner
Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606 Mail: frank.finner@invenius.de
Telefax: 0271 231 8608 Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651
Attachment
Terry Lee Tucker <terry@esc1.com> wrote:
> Joseph Brenner <doom@kzsu.stanford.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.
On Sun, Jun 04, 2006 at 02:24:32PM -0700, Joseph Brenner wrote: > 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. You'd think that, but it's not as straight-forward as all that. Quite a bit of code assume the current database, for example the cache of system for tables and attributes. The same tables with will have the same OIDs but different data. Once you get around to clearing all the caches and reloading them, flushing data from buffers, etc, it's not all that clear what you're saving over a full backend restart. It's been considered but just no-ones tried it yet. And in the mean time, something like pgpool will beat the pants off you because the shutdown of the old database and the connection of the new can overlap, and you'll be talking to the new database before the old has even noticed you're gone... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.