MIME-version: 1.0
Content-type: text/plain; charset=windows-1252
Subject: Re: new user on mac
From: Basil Bourque <basil.list@me.com>
In-reply-to: <147DA84F-359C-4382-A360-B6B8E2D7E04F@gmail.com>
Date: Wed, 19 Oct 2011 01:13:44 -0700
Content-transfer-encoding: quoted-printable
Message-id: <257F49CD-D20C-4AFC-BF0E-EC689C19B4CB@me.com>
References: 
 <CAJikGoAzNzxNd0XUZodH+Fobu05=sbFSxnEzyngzKD=ZFXWq-w@mail.gmail.com>
	<CAB5sPxZSS0ETDHLk=+EuK6inFz_FF52ogSdSHTTEJ2EYJBWG_Q@mail.gmail.com>
	<147DA84F-359C-4382-A360-B6B8E2D7E04F@gmail.com>
To: pgsql-sql@postgresql.org

>> 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?
>>=20
>> Coming from an Oracle world, I'm thinking of toad, sql developer, =
etc.
>>=20
>> 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. Surprising because 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:

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

=95 (Serious) Tools > Server Configuration > pg_hba.conf has a nasty =
anti-feature. When loading a saved conf file with incorrect syntax =
(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 the entries 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 the Unix 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, creating columns, creating =
a few initial rows of data, editing some field values, and so on.=20

When first starting out creating tables, I used the GUI dialogs in =
pgAdmin. Nowadays I take advantage of the feature where pgAdmin =
generates and shows you the SQL that would re-create the table on which =
you've clicked. When creating a new table, I copy the SQL from a similar =
table, paste into a text editor, and edit appropriately. Then I paste =
the SQL back into 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" from the 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 yet as pgAdmin is =
sufficient for now.

=3D=3D Caution =3D=3D=20

I'm not too clear on Postgres' defaults when installing Postgres, and =
when creating a new database. But I believe Postgres defaults to a =
character set appropriate to the platform. I explicitly choose UTF-8 for =
both, rather than depend on some 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 Postgres and pgAdmin.

=3D=3D Another Caution =3D=3D=20

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

=95 The character set to MacRoman.=20
  You may want to change the default to UTF-8.

=95 The newline/end-of-line to CarriageReturn.=20
  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 item to make the change =
happen.

--Basil Bourque=