books/sites for someone really learning PG's advanced features?

[Miles Keaton]

I'm switching to PostgreSQL from MySQL. Using the SAMs book called
PostgreSQL which has been great to skim the surface of the
differerences.

I had never even heard of things like triggers, views, and foreign keys before.

Any recommended books or websites (or exercises) that would really
help someone get to know not just the basics of how these advanced
features work, but some real in-depth insight into how to USE them for
real work?

(It's always hard to get used to actually using features you never
knew existed before.)

Thanks!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Mike Mascari]

Miles Keaton wrote:

I'm switching to PostgreSQL from MySQL. Using the SAMs book called
PostgreSQL which has been great to skim the surface of the
differerences.

I had never even heard of things like triggers, views, and foreign keys before.

Any recommended books or websites (or exercises) that would really
help someone get to know not just the basics of how these advanced
features work, but some real in-depth insight into how to USE them for
real work?

I'd start out with:

http://www.acm.org/classics/nov95/toc.html

Unfortunately, the ACM doesn't have the complete paper online.

Then read:

C.J. Date, An Introduction to Database Systems

Or skip over the Intro book and read:

C.J. Date, A Guide to the SQL Standard

Here's a good link for problems caused due to lack of normalization:

http://209.197.234.36/db/simple.html

You'll see that views and foreign keys are fundamental to ensuring
consistency and handling data with normalized base tables. You should
try to achieve logical consistency in your design without using triggers
through the use of domain constraints, column and table constraints and
referential integrity constraints. Failing to enforce consistency at
that point, triggers can be used to enforce such things as what Date
calls database constraints. I.e.: if a department has a budget of under
1000, there should not exist more than 5 employees.

Hope that helps,

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tino Wildenhain]

Hi,

On Fri, 2004-09-24 at 08:03, Miles Keaton wrote:

I'm switching to PostgreSQL from MySQL. Using the SAMs book called
PostgreSQL which has been great to skim the surface of the
differerences.

I had never even heard of things like triggers, views, and foreign keys before.
Smells like success story here :-)
Any recommended books or websites (or exercises) that would really
help someone get to know not just the basics of how these advanced
features work, but some real in-depth insight into how to USE them for
real work?

(It's always hard to get used to actually using features you never
knew existed before.)

The point is when you miss something and you know there must be a better
way to do things which motivates a change of a plattform.

I dont know many good books about this, but at least foreign keys
are a basic concept of RDBMS so you should find a lot about in the
literature. All other topics are something you will know when you
have a problem to solve. I'd recomment not using a feature just
because its so cool :-)
Postgresql has a lot of other helpful things like functions in
different languages - where you definitively will have to read
a lot, custom datatypes and operators and more.

Welcome to the world of postgres I'd say :-)

Regards
Tino
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Jeremy Semeiks]

On Thu, Sep 23, 2004 at 11:03:30PM -0700, Miles Keaton wrote:

I'm switching to PostgreSQL from MySQL. Using the SAMs book called
PostgreSQL which has been great to skim the surface of the
differerences.

I had never even heard of things like triggers, views, and foreign keys before.

Any recommended books or websites (or exercises) that would really
help someone get to know not just the basics of how these advanced
features work, but some real in-depth insight into how to USE them for
real work?

(It's always hard to get used to actually using features you never
knew existed before.)

Hi Miles,

It sounds like you're in the same place I was in 18 months ago. I
learned SQL (as it were) by reading the MySQL manual, and then I read
an advocacy post somewhere and realized that I was re-implementing in my
middleware all the stuff that the DBMS should've been doing for me
already.

The way I learned Postgres was by reading the documentation
cover-to-cover (<http://www.postgresql. org/docs>). There are lots of
really good examples in there on all the features you mention, but I
seem to remember that they were scattered all over the place, and
anyway I found the docs to be a pretty easy read. I never bothered
with any of the Postgres-specific books because, as I understood it,
there was nothing on the market at the time that covered the latest
version.

Actually, the feature I had the hardest time learning were server-side
functions (including triggers), because I couldn't find a good
interactive programming environment with syntax highlighting and all
the rest to test them out with.

Now, what really helped me to understand how to *query* databases was
the book _An Introduction to Database Systems_ by Chris Date, but that
book might distract you from learning Postgres, if you're an idealist,
because it really finds a lot of reasons to disparage SQL.

- Jeremy

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Paul Tillotson]

Miles,

(Just like someone else also commented), I was in a situation like
yours, and I found the postgres online documentation quite helpful. I
wouldn't read it cover-to-cover yet, but eventually you will want to
read every part of it.

Here is what I found particular helpful at the start:

SQL commands:
http://www.postgresql.org/docs/7.4/s...-commands.html

Concurrency control:
http://www.postgresql.org/docs/7.4/static/mvcc.html

Triggers:
http://www.postgresql.org/docs/7.4/static/triggers.html

Note that the section on triggers is a bit sparse, but you can see alot
of examples of triggers and their use by looking through the various
pages about pl/pgsql, pl/perl, etc. (which follow the section about
triggers)

Paul Tillotson

Miles Keaton wrote:

I'm switching to PostgreSQL from MySQL. Using the SAMs book called
PostgreSQL which has been great to skim the surface of the
differerence s.

I had never even heard of things like triggers, views, and foreign keys before.

Any recommended books or websites (or exercises) that would really
help someone get to know not just the basics of how these advanced
features work, but some real in-depth insight into how to USE them for
real work?

(It's always hard to get used to actually using features you never
knew existed before.)

Thanks!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Don Kelloway]

"Miles Keaton" <mi*********@gm ail.com> wrote in message
news:59******** *************** *@mail.gmail.co m...

I'm switching to PostgreSQL from MySQL. Using the SAMs book called
PostgreSQL which has been great to skim the surface of the
differerences.

I had never even heard of things like triggers, views, and foreign keys
before.

Any recommended books or websites (or exercises) that would really
help someone get to know not just the basics of how these advanced
features work, but some real in-depth insight into how to USE them for
real work?

(It's always hard to get used to actually using features you never
knew existed before.)

Thanks!

I too am just starting to learn, but I've found Practical PostgreSQL by
O'Reilly to be quite good and very informative.

--
Best regards, from Don Kelloway of Commodon Communications
Visit http://www.commodon.com to learn about the "Threats to Your Security
on the Internet".