Category: Types

MySQL Type Horrors

Although it is possible to demonstrate the problem in another DB system, I have deliberately chosen MySQL for this article because it literally lends itself to this kind of errors.

Example

A small, but powerful example. Just by looking at this you may guess that this post may have something to do with case sensitivity, but as you will see the problems go much deeper. Continue reading “MySQL Type Horrors”

Types and Typos in SQL [2]

In the previous post I demonstrated how small typos in SQL become large logical errors, and how a “proper” type system could help in preventing these. This time I explore how PostgreSQL’s enum data type can be leveraged to gain some data safety and prevent logical errors.

PostgreSQL’s ENUM

In PostgreSQL the enum data type offers some type safety, for example:

CREATE TYPE d1 AS ENUM
('1', '2', '3');

CREATE TYPE d2 AS ENUM
('1', '2', '3');

Enumerated values are labels, hence the quotation marks. Continue reading “Types and Typos in SQL [2]”

Types and Typos in SQL [1]

Almost anyone who has spent time working with SQL made — or had to fix — this kind of bug: 

-- two tables (sketch)
--
users {id, user_name}

trans {id, amt, cur, tx_time, user_id}

-- query with a typo
--
SELECT user_name
     , tx_time
     , amt
     , cur
 FROM users as u
 JOIN trans as t ON u.id = t.id -- <- typo !!
WHERE user_name = 'jack' ;

Try the query, here is the DDL with some data; note the wrong result. Continue reading "Types and Typos in SQL [1]"