Monday, February 05, 2007


psql is so good
in psql
there is a \h
which gives you sql query help,
so \h select
tehn as you are typing things out, hitting tab gives you options for what to use next sometimes
try typing
alter table [tab]
then it displays list of tables it can see.
then there is \? which lists other meta commands
like \dt shows tables
\l lists databases \
\dn lists schemas
\du lists users
well, for your setup mostly you have 1 users, one schema, and couple databases
when you are connected to one database in psql, \c newdbname
chages databases
after a few days you get used to the auto-complete features, and the \h things to help you, it feels like a gui sort of, but much much faster than pgadmin
though, i got into postgresql after being forced to work with oracle (yuk)
the only oracle gui at the time was toad, which is by Que$t $oftware.
and we never could afford to buy it
so we learned the oracle meta database,
which is oddly enough, tables and a database, to describe the database
postgresql has this too
the pg_catalog, where there are what looks like tables, to describe our user databases, schemas, tables, and our database objects.
and the \d commands in psql sort of are short cuts for this
but you can also do select .. from pg_* tables, and that gives us information on table features, and the columns, which is what pgadmin is doing behind the scenes for us to display their things all nicely formatted.
mysql, on the otherhand, does not have a meta database, but then what do you expect from junk :)
in version 3.3 the "show databases" command actually invoked a system command to "ls" (list files) in the mysql directory, since mysql used to (still does?) create databases as directories on the file system.
this has the horrible side effect of making table names Case Sensitive, which violates the SQL standard (lol, and mysql claimes to be sql compliant, but cant even get case insensitive table names :!)
we discovered that one time the hard way by migrating a mysql on windows app to mysql on unix, and of course developers will make code in different spots like
select * from MyTable
select * from mytable
select * from MYTABLE

No comments: