Re: new user on mac

>> I have a postgres 9.1 database up & running, no problem. Purely in
>> terms of writing sql (ddl, dml & pg/plsql), what tools are
>> recommended?
>>
>> Coming from an Oracle world, I'm thinking of toad, sql developer, etc.
>>
>> 1. psql & text editor of choice (if so, which one?)
>> 2. navicat
>> 3. textmate with pgedit
>> 4. eclipse plugin
>> 5. other?

>> +1 for pgAdmin3. If you have already used Toad, u would like to check it.

Being new to SQL (but old to other relational databases) and a Mac guy, I have found pgAdmin to work surprisingly well.
Surprisingbecause I have a knack for breaking/corrupting/crashing nearly every developer tool I start using as a
newbie.But pgAdmin has worked nearly flawlessly for me. It looks goofy from a Mac aesthetics perspective, but it works. 

I may have once had an inexplicable glitch, but after restart all was well. I've only been bitten by 2 recurring bugs:

• (Cosmetic) Changing font size for use on projectors in a meeting makes fonts bigger, but the rows of the Output Pane
ina SQL window fail to grow in height. 

• (Serious) Tools > Server Configuration > pg_hba.conf has a nasty anti-feature. When loading a saved conf file with
incorrectsyntax (usually I forget to put the slash+number on an IP address such as 127.0.0.1/32), pgAdmin parses the
file,identifies the flaw, and chooses to ignore the rule by not displaying it. Unfortunately, pgAdmin does NOT parse
theentries when entering or saving them. So if you screw up a rule: 
(a) You won't realize you saved incorrect syntax. To the user, it seems the rule you entered simply vanished.
(b) You can't fix it in pgAdmin. You'll have to gain access to the filesystem as the Postgres superuser (usually that's
theUnix user 'postgres'), and edit the file. This is not easy to do as a Mac GUI user. 
This issue has been acknowledged in the mailing lists.

But otherwise, pgAdmin has served me well for connecting to the Postgres server, creating databases, creating tables,
creatingcolumns, creating a few initial rows of data, editing some field values, and so on.  

When first starting out creating tables, I used the GUI dialogs in pgAdmin. Nowadays I take advantage of the feature
wherepgAdmin generates and shows you the SQL that would re-create the table on which you've clicked. When creating a
newtable, I copy the SQL from a similar table, paste into a text editor, and edit appropriately. Then I paste the SQL
backinto a SQL window in pgAdmin to execute. 

My usual choice in text editors is TextMate, running the surprisingly productive "Zenburnesque" Fonts & Color scheme in
Preferences,where you can force the text to be interpreted as SQL without bothering to save the file by choosing "SQL"
fromthe popup at the window's bottom frame. 

Other good text editors include JEdit (Java-based, free-of-cost), TextWrangler (free of cost), and BBEdit.
http://www.jedit.org/
http://www.textwrangler.com/products/textwrangler/
TextWrangler's commercial big-brother BBEdit is also a popular text-editor on Mac OS X.
http://www.textwrangler.com/products/bbedit/

Other Java-based IDEs are free-of-cost, run well on Mac OS X, and include SQL editing tools: IntelliJ, NetBeans,
Eclipse.

There are many other SQL tools that run on Mac OS X, especially the Java-based tools using JDBC. I've not tried them
yetas pgAdmin is sufficient for now. 

== Caution ==

I'm not too clear on Postgres' defaults when installing Postgres, and when creating a new database. But I believe
Postgresdefaults to a character set appropriate to the platform. I explicitly choose UTF-8 for both, rather than depend
onsome mysterious default. The docs are not clear about this, so I'm not sure about the best course of action. 

Other than this character set issue, one tip I've learned from experts is to trust the default settings of both
Postgresand pgAdmin. 

== Another Caution ==

Most text editors and IDEs on the Mac, both native and Java, have a tendency to default:

• The character set to MacRoman.  You may want to change the default to UTF-8.

• The newline/end-of-line to CarriageReturn.  You may want to change the default to Linefeed for the Unix convention.

Note that some tools do not change the character-set or newline of an existing file, or require that you choose a menu
itemto make the change happen. 

--Basil Bourque