conclusion: PostgreSQL works

Story: Five reasons why you should never use PostgreSQL. Ever.Total Replies: 3
Author Content
grouch

Mar 15, 2006
8:24 AM EDT 		The author does a very nice, polite job of debunking those 5 myths.

Not to start a flame war, but I cringe every time I have to use MySQL instead of PostgreSQL. At least as MySQL has advanced, it has become easier to convert stuff that's written to its quirks. I will never get used to it mixing display formatting with integer storage, though. Going in the other direction, the lack of foreign keys and sub-queries in MySQL causes me headaches. I've heard those are coming, so it's probably just old code that I'm dealing with.

Real choice is a grand thing. It's great that my choice doesn't deprive anyone else of their favorites.
number6x

Mar 15, 2006
10:25 AM EDT 		I like Postgres better than my SQL as well, but mySQL is not as bad as oft repeated rumors say.

mySQL has supported foreign keys since version 3 point something. mySQL supports transactions and about 99.99% of all the other things people says it doesn't support as well.

Wanna see?

First create a database for your tables...

> mysqladmin -u root create demo

Then whip up some sql to create two tables and save it in a file called something meaningfull like demo.sql, and add some data to the first table:

drop table if exists things; create table things ( id int not null auto_increment, title varchar(35) not null, primary key (id) )TYPE=InnoDB;

drop table if exists line_items; create table line_items ( id int not null auto_increment, things_id int not null, quantity int not null default 0, constraint fk_items_things foreign key (things_id) references things(id), primary key (id) )TYPE=InnoDB;

INSERT INTO things VALUES (NULL, 'The first thing'); INSERT INTO things VALUES (NULL, 'The second thing'); INSERT INTO things VALUES (NULL, 'The third thing');



Create the tables with the command:

> mysql -u root demo <demo.sql

Then go to the mysql prompt for our database with the command: > mysql -u root demo

You should see: mysql>

So try to add something to the line_items table:

mysql> INSERT INTO line_items VALUES (NULL, 1, 10); Query OK, 1 row affected (0.02 sec)

No problems...

Now try adding one with a foreign key that does not exist:

mysql> INSERT INTO line_items VALUES (NULL, 4, 100); ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails mysql>

Houston, we have a problem Foreign key constraints do work in mySQL! So try it again with a valid foreign key:

mysql> INSERT INTO line_items VALUES (NULL, 3, 100); Query OK, 1 row affected (0.02 sec)

All in all, I have more experience with PostgresSQL, but don't believe most of the bad press about mySQL, it just isn't true.
grouch

Mar 15, 2006
11:26 AM EDT 		Thanks number6x. That tells me just how old the code is that I was working with. That also tells me that abstracting for either MySQL or PostgreSQL is much easier than it used to be.
number6x

Mar 16, 2006
5:04 AM EDT 		Oh grouch,

It could also be that the tables you have are type myISAM. This is a native mySQL format used for simple tables that uses less overhead, but also lacks advanced features like transactions and FR key referential integrity.

to alter a myISAM table into a InnoDB table use the command:

alter table table_name TYPE=InnoDB;

This will convert a table named table_name into InnoDB.

-Sean

Posting in this forum is limited to members of the group: [ForumMods, SITEADMINS, MEMBERS.]

Becoming a member of LXer is easy and free. Join Us!