This chapter at InformIT covers the basic operation of PostgreSQL, including naming conventions, creating a database, and indexing.
PostgreSQL SQL Syntax and Use
About The Author
Eugenia Loli
Ex-programmer, ex-editor in chief at OSNews.com, now a visual artist/filmmaker.
Follow me on Twitter @EugeniaLoli
Won’t complaing about any posts on Postgresql.
This article is from PostgreSQL, 2nd Edition by Korry and Susan Douglas; probably the best general book on PostgreSQL out at the moment, and I think currently the only one to cover the new capabilities in version 8.
I haven’t really had the time to give it a thorough go over though. Is it as good a candidate to replace MySQL as it seems? Are there fundamental differences I should know about?
I appreciate any insight from those who know both.
trolling?
anyway, there are quite a few differences between postgresql and mysql.
A lot of apps created to work with mysql’s broken sql implementation might not work with postgresql.
I made the mistake of learning sql from the mysql handbook.
Postgresql has a lot more features than mysql, including stored procedures(scripting within the database) and user defined datatype(I’ve had lots of fun with these).
Postgresql is a much better database than mysql. But some people find it a bit more difficult to use because it is less forgiving on bad sql. (I find it easier to use due to this same reason)
– Jesse McNelis
I have to fully agree with jessta. MySQL may have the spotlight, but it’s PostgreSQL who has the goods.
PostgreSQL is leaps and bounds more ANSI SQL compliant than MySQL, and the feature set is much broader. See this link for a decent high level comparison of the major players in the RDBMS realm:
http://troels.arvin.dk/db/rdbms/
…and, of course, see the MySQL Gotchas page:
http://sql-info.de/mysql/gotchas.html
Note: this website is an equal-opportunity criticizer, and also has a PostgreSQL Gotchas page and an Oracle Gotchas page. But, there is just so much more to work with in the MySQL section…
yet another pointless OSNews post…
How much money do you get from InformIT and IBM ?
Is it just me or is ELQ contributing more lately? Let me start a rumour that I think she’ll come back soon… I’m betting she misses it. I for one rewelcome our greek OSNews overlord.
Actually Postgres and Firebird are the only two real SQL OSS databases, performancewise (once you have to apply joins and subselects which is the normal case anywy)
Postgres is years ahead of MySQL, featurewise anyway, it has a better license. Many people tend to forget that MySQL has a GPL lockin which you can buy yourself out.
It is more stable, I have seen corrupted MySQL repos several times under high load, I have yet to see one in Postgres.
Solid release cycle, with upfront feature planning and no you dont need that you can use something else instead for 10 years from the devs. The only thing you hear about a missing featur is either, it is planned, or currently not on our list due to massive time constraints.
The errata list is much less full of critical bugs than MySQLs because the devs rather have a feature stable than to push the latest performance issue.
the list is pretty endless, why Postgres should be preferred over MySQL.
MySQL is a half toy database which should never have gotten the attention it got. Even in its early days there were better OSS dabases, nowadays it is sort of a non brainer just not to use it, because the alternatives are really better. The only downside is that you do not get I will learn postgres in 5 days books for a dime a dozen like you get it for Mysql.
PostgreSQL is my DBMS of choice. It is nothing hort of excellent, far far better than MySCO.
The PostgreSQL documentation is IMHO well done. It contains a primer as well as references.
So what is the intention of this article?
Carsten
PostgreSQL doesn’t compare to MS Access, Microsoft are still the best.
get out of here – Access is for kids play. PostgreSQL is for real work.
They’re different tools and not drop in replacements for each other. If you’ve developed something for mySQL, there’s little reason to try and port it to Postgres. There are some simple changes (like INT auto increment to SERIAL) that make it a pain and you won’t see gains unless you are using Postgres’ features which would break mySQL compatibility. If you are developing something new, you might want to look at Postgres.
Postgres can do a lot of cool things like on delete cascade. For those who have written web apps with mySQL, you know that when you delete something from table A (let’s say a user) and you want to remove all the things that user did on the site (like internal messages maybe) in table B, you have to do a delete command on each table to remove the stuff. Not a big deal unless you get to a system with hundreds of tables and very complex relations that you might not realize when extending it. With Postgres, you can store references in the database (so that messages in table B reference their cooresponding user in table A) and the database will enforce that reference. With on delete cascade, the database would delete the messages anytime the user was removed from table A.
References can help in other situations too. For example, what if you want to re-ID every user on the system. A reference would mean that their messages would follow them even though the ID changed. Spiffy.
Of course, mySQL is getting more and more of these features so it’s becoming less of a big deal if you’re running a new version of mySQL, say 4.1 (I still run 3.23). Even at 4.1, Postgres can do more neat things, but mySQL is getting better.
Yes, they are different tools, but when you develop, you should not develop for any specific tool like MySQL or for that matter Postgresql, but for the SQL standard. Deviations from the standard should only be made when the tool so requires.
The less deviatons the and the more things you can handle close to the data in a standardized way better.
If things can be done close to the data, i.e. in the database the easier it is to write different type of interfaces to your information. The more you have to do in surrounding code, the more tests you will have to apply to make sure that each interface you make to your system follws all specified business rules.
This is why most people that have knowledge about the SQL standard, MySQL, and Postgresql finds Postgresql the better tool.
>Yes, they are different tools, but when you develop, you should not develop for any specific tool like MySQL or for that matter Postgresql, but for the SQL standard. Deviations from the standard should only be made when the tool so requires.
Incorrect. If you take this approach, you’ll never be able to take advantage of the specific features that increase performance of your engine. For example, stored procedures are CRITICAL in our applications due to the thin client requirements we have. Each DBMS has a different syntax for this, and therefore if I simply say NO to using SPs, then I end up with a lower performance solution just to be database agnostic. It doesn’t make sense.
Adjust your strategy based on your needs. The statement ‘you should not develop for any specific tool like MySQL’ is assuming minimal performance need and that you are willing to drop to the lowest common denominator for your solution. My clients have needs that exceed that level and therefore your point is not valid.
Myles
Using intricate optimizations will raise the cost of maintanance of the system over time, and will require more specialized knowledge. Such specialized knowledge will come at a price. Even with todays low saleriries it is often better economy to throw more hardware at the problem.
By the way, if you are using stored procedures MySQL doesn’t have much to offer compared to Postgresql. First of all they are not available in production ready versions of MySQL, and even if you use MySQL 5.x they are a lot less powerful than what is offered in Postgresql.
So, by using a production ready version of MySQL you would have excluded yourself from using stored procedures, and then you could just as well use a database where whatever sql statmensts you write can be written to conform to sql standards.
Still believe Firebird is the best. Not as light as mySQL, not as heavy as Postgre.
Postgres is far superior to Firebird, I could list about 20 reasons why it is better, but I won’t because it’s obvious you have not used a newer version of Postgresql.
Ok, I will give you one: documentation, Postgresql is incredible, while firebird stinks.
I will say firebird is better for a embedded type app because they have the whole server in a dll going, but for pure client/server enterprise apps postgresql is king of the Opensource RDBMS.
>Postgres is far superior to Firebird, I could list about 20 reasons why it is better, but I won’t because it’s obvious you have not used a newer version of Postgresql.
Ok, I will give you one: documentation, Postgresql is incredible, while firebird stinks.
Try Firebird with Helen Borrie’s The Firebird Book, and you have your problem solved and a lower maintenance, lower headache and lower cost of ownership DBMS to boot.
Myles
Postgres is far superior to Firebird, I could list about 20 reasons why it is better, but I won’t because it’s obvious you have not used a newer version of Postgresql.
All depends of the needs, for my needs Firebird is better, I don’t need all the power of Postgre, that will only add bload to my software, And yes I’ve used Postgre, after the native Windows version I was decided to use it but I release that many of the things it has are already in Firebird and it was like 5 times slower and not_as_easie_to_deploy as Firebird.
For documentation I must agree with you, because is pour, I now well Firebird because I used Borland’s Interbase before and since till now its 95% compatible you can use its documentation.
All depends of the needs.
In general I prefer Postgres. Better documentation, more features, etc…
But I am going to agree with Yuske here on performance. I took a few of our applications that were written for Oracle, and ported them to Postgres 8 and Firebird 1.5 for performance testing. Our testing was done on Windows, and Firebird out performed Postgres. It was not 2x faster, but about 1.5x faster. This was based on very high load tests, with complex sql (inserts, updates, and deletes).
Also I give Firebird extra points for it’s embedded abilities. Too bad Firebirds Documentation is total crap!! You need to buy “The Firebird Book” if you want any real docs on Firebird, and it is not quite as feature rich as Postgres.
I use MySQL for my home projects, and let me say that it is a toy. My personal little web apps don’t need anything more then toyPower, but let not kid ourselves it is still a toy. I have only used up to 4.1. Performance when using apps that had complex sql under load was a joke, inserts of large amounts of data using transactions under load was a joke, mySQL lacks features needed by many corporate business apps, and I’m cirtanly not going to pay them for it! Not having an LGPL JDBC driver for newer versions is having a cooling effect on their growth.
But I am going to agree with Yuske here on performance. I took a few of our applications that were written for Oracle, and ported them to Postgres 8 and Firebird 1.5 for performance testing. Our testing was done on Windows, and Firebird out performed Postgres. It was not 2x faster, but about 1.5x faster. This was based on very high load tests, with complex sql (inserts, updates, and deletes).
Performance on postgres is a bit of an elusive target. The stock postgres tends to have performance issues and you really need to spend a substantial amount of time tweaking both configuration and indexing to get something that competes with the other major players. But, when you do the work, you will find that you can make postgres fly – you just need to learn how to tweak it (and that’s not an easy task 🙂 )
Not having an LGPL JDBC driver for newer versions is having a cooling effect on their growth.
I second that. I have been put off of MySQL by this, and I just won’t use it in a business setting. I did contact the MySQL team about trying to license it for a binary redistribution in a commercial app – it was just much cheaper to use postgres, and not have to deal with all the crazy mysql issues in the first place. 🙂
Hi Nathan_c,
Can you give me some links to get me started on Postgres performance tuning. I did spend some time tuning my Firebird and Postgres setups, mostly from the point of view of setting up indexes, and following general guidelines for a database of my size.
Where would I go to find more indept tuning info for Postgres. Postgres is my OSS database of choice, so I would like to use it to the best of its abilites.
Thanks,
Here’s a list of links from my bookmarks…
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www2.linuxjournal.com/article/4791
http://candle.pha.pa.us/main/writings/computer.html
http://www.postgresql.org/docs/8.0/interactive/performance-tips.htm…
http://pgsqld.active-venture.com/runtime-config.html
http://www.budget-ha.com/postgres/
http://www.powerpostgresql.com/PerfList/
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e…
If you google postgre performance you will get most of these. This should get you off to a good start. However, if you are really interested in performance tuning, join one of the pg mailing lists and ask questions (or read through them). If you work for a company that wants to spend some cash and really wants to get great performance, there are a few companies that will consult for you, or I’m sure one of the pg devs would be willing to help you out (and I’m sure they know all the tricks in the world 🙂 )…
I am trying to use mysql on XP, Linux, and on a web-site.
I can not get mysql to keep working anywhere. Constant errors like “can’t connect” “can’t find socket” “no database loaded” etc. Many times I have had everything working, turn of the PC, turn the PC on later, and everything is broken.
I check all setting a dozen times, and everything looks right. I go to the forums and find dozens of posters with the same problem, but nobody with any answers.
This is all just from my limited experience. I have even less experience with PostgeSQL, but I don’t see how postgre could be any worse. I am looking forward to trying sqlite.
Also, I don’t feel good about mysql partnering with scox.
There is a new book in the works for high-performance PostgreSQL setup and administration, by Josh Berkus and Joe Conway:
http://www.powerpostgresql.com/
I see a lot of comments about PostgreSQL’s (lack of) speed. Most of this is a legacy of PostgreSQL’s default configuration, which is quite conservative. Imagine that you want to install PostgreSQL on a Pentium 90 with 32 MB RAM, and you’ve about got the picture. Here are a few places where you can learn how to configure PostgreSQL to take better advantage of RAM, disk speed, etc… on a modern dedicated server.
http://www.powerpostgresql.com/PerfList/ (from the book mentioned above)
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://forums.devshed.com/t84334/s.html
For a thorough comparison of open source databases (Firebird 1.5.2, Ingres r3 3.0.1, MaxDB 7.5.0.23, MySQL 4.1.10 and PostgreSQL 8.0.1), take a look at http://www.fabalabs.org/research/papers/FabalabsResearchPaper-OSDBM…. It is a lenghty document that doesn’t fall into the all-too-common ‘my database is better than yours’ kind of ‘documentation abuse’.
I was just trying to install it. First, I couldn’t log in, no matter how much I tried. I am pretty sure I got the username/password right.
I re-started the install program, and selected remove, it seemed to work. I went to re-install, and got the error message “specified data directory is not empty”
Just like with mysql, lots of people seem to have this problem, nobody seems to have any answers.
Now what am I supposed to do? How do I get this off my system. This sucks.
Now what am I supposed to do? How do I get this off my system.
I am just guessing here, but you’re running Windows, right? “specified data directory is not empty” means what it says; PostgreSQL doesn’t like to install itself over a directory that already has previous PostgreSQL data. When you uninstall PostgreSQL, it uninstalls the program, but doesn’t delete the PostgreSQL data directory, thus preserving whatever databases you set up (a default install at least installs the ‘template1’ database).
So, if you find that directory and delete it, then you can reinstall. Or, you can just choose to install PostgreSQL in another custom directory.
If you really want PostgreSQL help, try subscribing to the pgsql-novice and pgsql-general mailing lists:
http://archives.postgresql.org/pgsql-novice
http://archives.postgresql.org/pgsql-general
PostgreSQL people are usually quite friendly and willing to help. I would hold off on the “PostgreSQL sucks” flames, though.