Wednesday May 16, 2007

I've had my fill of MySQL's quirks, so I thought I'd plumb for PostgreSQL's. So many things that MySQL is fast and loose about, PostgreSQL is strict and correct. However, I was fiddling around with PostgreSQL's equivalent to MySQL's enum and found what I would expect a strict RDBMS to be strict about... not so strict.

PostgreSQL does not have enum but there are a few different ways you can define your own data types and constraints and therefore prescribe your on constrained data type. This table definition will confine the values in 'selected' to 5 characters with the only options available being 'YES', 'NO' or 'MAYBE':

ikallen=# create table decision ( selected varchar(5) check (selected in ('YES','NO','MAYBE')) );
CREATE TABLE
ikallen=# insert into decision values ('DUH');
ERROR:  new row for relation "decision" violates check constraint "decision_selected_check"
ikallen=# insert into decision values ('CLUELESS');
ERROR:  value too long for type character varying(5)
ikallen=# insert into decision values ('MAYBE');
INSERT 0 1

So what's wrong with this:

ikallen=# create table indecision ( selected varchar(5) check (selected in ('YES','NO','MAYBE SO')) );
CREATE TABLE
ikallen=# insert into indecision values ('MAYBE');ERROR:  new row for relation "indecision" violates check constraint "indecision_selected_check"
ikallen=# insert into indecision values ('MAYBE SO');
ERROR:  value too long for type character varying(5)
ikallen=#

Well, I think so.

One of the cool things about PostgreSQL is the ability to define a constrained type and use it in your table definitions:

ikallen=# create domain ynm varchar(5) check (value in ('YES','NO','MAYBE'));
CREATE DOMAIN
ikallen=# create table coolness ( choices ynm );
CREATE TABLE
ikallen=# insert into coolness values ('nope');
ERROR:  value for domain ynm violates check constraint "ynm_check"
ikallen=# insert into coolness values ('YES');
INSERT 0 1

Contrast with MySQL's retarded handling of what you'd expect to be a constraint violation:

mysql> create table decision ( choice enum('YES','NO','MAYBE') );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into decision values ('ouch');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> select * from decision;
+--------+
| choice |
+--------+
|        |
+--------+
1 row in set (0.00 sec)

mysql> select length(choice) from decision;
+----------------+
| length(choice) |
+----------------+
|              0 |
+----------------+
1 row in set (0.07 sec)

mysql> insert into decision values ('MAYBE');
Query OK, 1 row affected (0.00 sec)

mysql> select * from decision;
+--------+
| choice |
+--------+
|        |
| MAYBE  |
+--------+
2 rows in set (0.00 sec)

mysql> select length(choice) from decision;
+----------------+
| length(choice) |
+----------------+
|              0 |
|              5 |
+----------------+
2 rows in set (0.00 sec) 

There are a few things that MySQL is really good for but if you want a SQL implementation does what you expect for data integrity, you should probably be looking elsewhere.

postgresql   mysql   rdbms   databases