[1] http://www.craigkerstiens.com/content/

[2] http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos...

http://www.postgresguide.com

http://www.postgresweekly.com

export EDITOR='subl -w'

I suppose osx built-in installation may cause conflicts.

http://stackoverflow.com/questions/12472988/postgres-could-n...

postgres app fixed it

This is a consistent source of trouble when installing Postgres. Usually, this involves looking up the right places to modify the path and sometimes needing to change permissions/ports.

While it is really easy to install most of the time, I'd say the Postgres.app works well for people who aren't developers but need to use PostgreSQL.

initdb /usr/local/var/postgres -E utf8

Also if you are working with rails, add host: localhost in your database.yml file and remove the user password. That should be all you need.

http://postgresapp.com/

This is probably "elitist" of me but I feel like someone who isn't comfortable with the command line should be learning that before they tackle Postgres.

If you're comfortable with command line, you really can't go wrong on most Linux flavors with the great documentation on the project website[2].

[1] http://www.enterprisedb.com/products-services-training/pgdow...

[2] http://www.postgresql.org/docs/9.2/static/install-procedure....

If someone is learning off of, say, Windows, their experience may possibly be quite different to what they will experience if they run this within a VM.

They are talking about how other installation methods than Postgres.app can be difficult. Which is exactly my point: If you're on Mac and you want to get started with PostgreSQL easily, Postgres.app is the way to go.

The reason is to be able to customize your build and/or apply patches. Tom Lane fixed a production bug I reported in hours and this got me running fairly quickly.

Or: https://news.ycombinator.com/item?id=6246393

As someone who primarily works with Rails, I feel like I use about 1% of PostgreSQL's power.

Check out this blog post: http://blog.remarkablelabs.com/2012/12/a-love-affair-with-po...

When I first got to Rails it seemed that databases were considered as a bothersome but unavoidable necessity; flat files with a funny accent, instead of an essential and powerful ally in the fight against entropy and error.

Some poking around in the current documentation seems to suggest that FKs have been absorbed into ActiveRecord.

Interesting link, either way.

As much as I like Rails, I sometimes wish it were designed with the database playing more of a role than just dumb storage. Defining things like validations in Ruby is certainly nice and easy, but the database is much better suited to actually enforcing such constraints.

In your webapp using POSTGRESQL, are you actually using SQL statements in your code to retrieve the data? Or is there some other way to get at it?

Rather than me explaining it here, the rails guides[1] give you a good overview.

[1] http://guides.rubyonrails.org/active_record_querying.html

- CHECK constraints. I have a gem for setting up foreign keys and CHECK constraints in Rails:

    https://github.com/pjungwir/db_leftovers

    class Publisher
      scope :with_no_books, -> {
        where <<-EOQ
          NOT EXISTS (SELECT 1
                      FROM   books b
                      WHERE  publishers.id = b.publisher_id)
        EOQ
      }
    end

- PostGIS: http://blog.daniel-azuma.com/archives/60

- hstore/json columns.

http://pgadmin.org/

Don't be intimidated by all the options it offers. It makes things really easy and - more importantly imho - shows you the SQL it runs for every command.

So far it seems a great way to learn pgsql coming with some background in SQL, if you want it to be.

I'm planning to cover more things about PostgreSQL, mostly using pg_dump, pg_restore, pg_upgrade, initdb etc., just the regular things you should know when using it on your own VPS.

I recommend this guide.. it's a good start: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serve...

Also, seconding rgbrenner re: "Tuning your PostgreSQL server" article.

Especially defaults for the most popular EC2 instance types.