[GENERAL] Connect without specifying a database?
Hi All, Quick question: Does PGSQL provide a mechanism by which to connect to the server without specifying a database? I saw a thread back in 2005 that said "no" (http://archives.postgresql.org//pgsql-interfaces/2005-02/msg00031.php ), but I was curious as to whether that's changed. Basically I need to be able to dynamically determine what databases exist etc before performing certain actions in my application. Thanks, Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 11:39 AM, Raymond O'Donnell wrote: The answer is still "no". :-) The usual thing it to connect to the "postgres" database (or to "template1" in older versions of PG), and then you can issue queries to see what's there. You're pretty much guaranteed that one of those databases will exist (they're created by initdb), unless whoever installed the server did something strange. Ray. Hrm...Ok, well, for the record, I'm moving some systems from MySQL to Postgres and am in the process of getting familiar with it. I was hoping there were some higher level management items like "show databases", and "show tables", etc in Postgres as well. I also didn't want to depend on there being a table there that I needed to "get in the door" with, so to speak. I'm guessing no one in the community sees this addition as desirable (or feasible)? Thanks for the replies, Michael
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 11:56 AM, Tom Lane wrote: There are, although we don't spell the commands like that. This is not relevant to the question of whether it makes sense to connect without being connected to a specific database. It doesn't, because the catalogs that you have to inspect to find out anything are database-specific. regards, tom lane I'm sorry, I'm confused by your reply. I may have said "table" when I meant "database". I believe it absolutely *does* make sense to connect without specifying a database first. What if you hit that edge case where there are in fact no databases? I suppose you could always run "createdb" just in case before you do anything else, but that seems counterintuitive. What do you mean when you say the "catalogs...are database- specific" ? If I'm understanding what you're saying, my whole point is that I don't want to be tied to a database to do any kind of querying about the PG engine itself. Does that make sense? Best, Michael
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 12:56 PM, Tom Lane wrote: There is no such edge case. DROP DATABASE has to be issued while connected to some database, and it won't let you drop the DB you're connected to. And CREATE DATABASE has to be issued while connected to some database, so createdb still has to have a default database to connect to. There really is no state in Postgres corresponding to "connected but not connected to any particular database". It does all hang together. You will need to lose a lot of MySQL preconceptions along the way, I fear. regards, tom lane I think our first issue is semantics and our second is paradigm. Hopefully I'm simply misunderstanding what you're saying, but what sense does it make to have to connect to an unrelated DB in order to query about others? Basically, I have some applications that will simply use PG as a backend. That application needs to ask the engine manager (whatever that means in in postgres speak) and see if relevant databases already exist. If they don't then it needs to create them. So, how does needing to connect to a database before querying about existing databases make any sense? MySQL aside, it seems an extra constraint/ step for naught. Perhaps I asked the wrong question in the beginning -- I do apologize. Maybe I should have asked for an external application that has the ability to talk to the PG engine. I believe John R. Pierce provided me with what I need in his last post -- that of listing DBs via a "psql -l". Thanks, Michael
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 1:49 PM, Sam Mason wrote: As others have said; the design of PG is such that it's built to assume you're always connected to exactly one database. I'd guess this is an artifact from a long time ago when PG didn't have multiple databases. -- Sam http://samason.me.uk/ Yes, I believe that this gets at the heart of some of the issue. I think there is some confusion on both sides. Basically, I was under the impression that PG had some overarching database server to which one could connect without needing to specify a database. While, yes, this might be "unremarkable", it is still an extra constraint for no *obvious* reason. I could understand if it was an *option* based on the need for security, etc., but the strict enforcement of this paradigm seems a bit dated. Perhaps the docs make reference as to why, and I simply haven't come across it yet. That said, "psql" provides me with what I need for now. Thanks, Michael
