#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Location
    INDIA
    Posts
    13
    Rep Power
    0

    Question Basic advantage of PostgreSQL over SQL ?


    Respected Sir's,
    I am new to PostgreSQL sir.
    so i want to know in detail sir.
    would you please tell me the advantage of using PostgreSQL over SQL .
    please tell me in detail sir !

    yours,
    PRAGASAM.A
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13
    You are talking about Microsoft SQL Server, right?

    The first advantage is that Postgres is free! Not only that, it runs on many types of UNIX, as well. I currently run Postgres on a FreeBSD machine. Now that combination makes for a STABLE server.

    I like to think of Postgres as the Oracle of open source databases. The two are very similar to each other.

    Other advantages are that it's fast, very stable, and is one of the truest SQL standards compliant RDBMSes out there.

    Having a standards compliant database is an advantage because it makes it alot easier to port your code to other databases that try to adhere to SQL standards.

    Postgres is a very nice system, and really it's not very difficult to learn. If you know Oracle, this is a breeze. When I need help, I reference some of my PL/SQL books for tips on forming alot of the SQL code on Postgres.

    I have no experience with conversions from SQLServer, but I've heard they can be rough, since the Transact-SQL in SQLServer sort of "cheats" sometimes. For example, sometimes you can perform mathematical operations on data with different datatypes in SQLServer. SQLServer coded some functions like DATEDIFF to make it possible to deal with these different datatypes. In a standards compliant database, you need your datatypes to be the same to perform an operation on them. So, I have heard accomodating for some of these "cheat" functions can be a pain in SQLServer. So you might want to keep that in mind if you're planning on migrating.

    I'm sure somebody who's more experienced in SQLServer can give you better information than I.

    Oh, I should also mention that the Postgres user community is extremely sophisticated, and they are very kind and generous with their time as well.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Austin,TX
    Posts
    62
    Rep Power
    0
    Ted Striker,

    Well said! he makes it easy to understand the power and performance of postgresql at a fraction of the cost of ms sql server.

    open source, transaction support, and immense support by php of postgresql, i wouldn't try anything else
  6. #4
  7. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13
    How about MySQL? What's the major advantages of PostgreSQL over MySQL?
    I have yet to find the right way to do it.

    www.genedavinci.com
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    See this thread: MySQL vs PostgreSQL
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13
    Hey rycamor you answered my question regarding whether it was good practice to still put integrity contraints into the database. Once again it seems planning for the future and portability comes into play.

    Currently I have PHP doing all the error checking for proper datatypes, length of the string, etc.

    I was also going to ask if there were any problems with having NULL values in a FOREIGN KEY column, but Bruce (the Man) explains that this is not only okay, but that this value must either reference the primary key, or be NULL.

    For multi-column FOREIGN KEYS, however, you need to take extra precautions in your planning:

    http://www.ca.postgresql.org/docs/aw...00000000000000
    Last edited by Ted Striker; July 3rd, 2002 at 05:35 PM.
  12. #7
  13. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Yes, NULLs in a foreign key are legal in SQL, if you define your FK to allow them. However, I would be very careful about using them for anything meaningful. In general, the guys at www.dbdebunk.com tend to caution against this practice, since it can lead to some sticky issues down the road.

    And, why would you want to save an absence of information? One of the main reasons for FK support is to prevent orphaned records, which is exactly what you get with NULL foreign keys.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Thumbs up


    Hey, good looking out rycamor.

    Instead of NULL, I've created a special code for nonapplicable values.

IMN logo majestic logo threadwatch logo seochat tools logo