Thread: mysql command equivalents?
At the continuing urging of an acquaintance (and with the uncertainty around Oracle's acquisition of Sun) I'm trying to give PostgresSQL a try, but the difference in commands is seriously frustrating. Is there a nice single table somewhere listing MySQL equivalents, e.g. "USE database" == ? My google-fu is failing me. (And even the docs don't seem to have anything for that particular example.) A cheat sheet would speed things up immensely. TIA! -- Hassan Schroeder ------------------------ hassan.schroeder@gmail.com twitter: @hassan
On Tue, Oct 13, 2009 at 2:20 PM, Hassan Schroeder <hassan.schroeder@gmail.com> wrote: > Is there a nice single table somewhere listing MySQL equivalents, > e.g. "USE database" == ? My google-fu is failing me. (And even the > docs don't seem to have anything for that particular example.) I'm not familiar with a cheatsheet myself (but there may be one somewhere), but I have some information about your current problem. PostgreSQL doesn't have the functionality to change a connection from one database to another with a simple command. The client app has to drop the connection and open a new connection to the other database. However, you can achieve similar functionality to this by using multiple schemas in a single database. Then to switch focus from one schema to another you would use "SET search_path = " <name of schema>. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Tue, Oct 13, 2009 at 2:20 PM, Hassan Schroeder <hassan.schroeder@gmail.com> wrote: > A cheat sheet would speed things up immensely. http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL Here is what I was able to find. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Richard Broersma <richard.broersma@gmail.com> writes:
> On Tue, Oct 13, 2009 at 2:20 PM, Hassan Schroeder
> <hassan.schroeder@gmail.com> wrote:
>> Is there a nice single table somewhere listing MySQL equivalents,
>> e.g. "USE database" == ? My google-fu is failing me. (And even the
>> docs don't seem to have anything for that particular example.)
> PostgreSQL doesn't have the functionality to change a connection from
> one database to another with a simple command. The client app has to
> drop the connection and open a new connection to the other database.
Note that practically all clients do offer that functionality easily
--- for instance, in psql it's "\c databasename". But without knowing
what client code you're using it's hard to offer advice.
Also, as Richard noted, mysql's "databases" are really closer to pgsql's
"schemas".
regards, tom lane
2009/10/13 Hassan Schroeder <hassan.schroeder@gmail.com> > > A cheat sheet would speed things up immensely. > Not really a cheat-sheet as much, but this might help: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL
Hassan Schroeder <hassan.schroeder@gmail.com> writes:
> On Tue, Oct 13, 2009 at 2:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Note that practically all clients do offer that functionality easily
>> --- for instance, in psql it's "\c databasename". �But without knowing
>> what client code you're using it's hard to offer advice.
> Thanks, that's what I was looking for. And yes, my bad for not
> specifying I was using 'psql' -- didn't realize there was more than
> one command-line interface. (Are those listed anywhere?)
Not sure there is more than one, but there are certainly GUI clients,
such as pgAdmin.
>> Also, as Richard noted, mysql's "databases" are really closer to pgsql's
>> "schemas".
> Sorry, can you expand on that, or give me a pointer to an explanation?
> If a pgsql schema is the equivalent of a mysql database, what's a pgsql
> "database"? Why would you choose one over the other?
AFAIK mysql doesn't have anything that directly corresponds to a PG
database. In PG, a database is a separate set of system catalogs.
There is some infrastructure it shares with other databases in the
same installation, but not much. If you have a table in one database,
it is simply not possible to get at it while connected to a different
database. This is unlike schemas, which are just a naming hierarchy.
If you're accustomed to doing "select * from foo.bar" to get at a
table named bar in a mysql database named foo, you'd want to handle
that in PG by setting up a schema named foo. So in that context,
"USE something" corresponds to "SET search_path = something", just
as Richard suggested.
Usually you'd use a separate database for an application or user
that you want to keep pretty well insulated from others.
regards, tom lane
[ please keep the list cc'd on replies ]
Hassan Schroeder <hassan.schroeder@gmail.com> writes:
> So, if you're accessing via code, you have to establish a connection
> to a specific PG database, and then explicitly set the search_path if
> that db has more than one schema -- is that right?
In general, yeah. If you like, you can preset the desired search_path
value as a per-database or per-user setting, and then your code doesn't
need to think about it. However, if you're accustomed to issuing
USE explicitly, I don't see why you wouldn't just change that to SET
search_path.
regards, tom lane
On Tue, Oct 13, 2009 at 3:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > [ please keep the list cc'd on replies ] Sorry, just realized 'reply to list' wasn't used here automatically. > Hassan Schroeder <hassan.schroeder@gmail.com> writes: >> So, if you're accessing via code, you have to establish a connection >> to a specific PG database, and then explicitly set the search_path if >> that db has more than one schema -- is that right? > > In general, yeah. If you like, you can preset the desired search_path > value as a per-database or per-user setting, and then your code doesn't > need to think about it. However, if you're accustomed to issuing > USE explicitly, I don't see why you wouldn't just change that to SET > search_path. Well, apples and oranges; I use `USE databasename` to change DBs frequently in my command-line client, but never in code -- that's just a connection (pool) request. Thanks for the extended explanation! -- Hassan Schroeder ------------------------ hassan.schroeder@gmail.com twitter: @hassan
Hassan Schroeder wrote on 13.10.2009 23:20: > A cheat sheet would speed things up immensely. Check out: http://techcheatsheets.com/tag/postgresql/ http://www.postgresqlguide.com/postgresql-cheat-sheet.aspx