What's That Noise?! [Ian Kallen's Weblog]

Main | Next day (May 17, 2007) »

20070516 Wednesday May 16, 2007

PostgreSQL Quirk: invalid domains

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
I don't want to hear any whining about how diff-fi-cult constrained types are. Welcome to the NBA, where RDBMS' throw elbows. The flexibility you get from loosely constrained types will come back to bite you on your next programming lapse.

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=#
'MAYBE SO' is in my list of allowed values but violates the width constraint. Should this have ever been allowed? Shouldn't PostgreSQL have complained vigorously when a column was defined with varchar(5) check (selected in ('YES','NO','MAYBE SO'))? Yes? No? Maybe?

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
Coolness!

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) 
Ouch, indeed. Wudz up wit dat?

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.

       

( May 16 2007, 07:33:00 PM PDT ) Permalink