Thread: books/sites for someone really learning PG's advanced features?
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!
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
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
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
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/static/sql-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 >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 > > > >